Questions tagged [group-by]

GROUP BY is a command in the SQL relational database standard for collapsing a group of rows that share common field value(s) into a single row. Aggregate functions can be performed on other fields in the group, such as SUM() or AVG(), to collate related data into a single value.

1
vote
2answers
448 views

In SQL, why is “select *, count(*) from sentGifts group by whenSent;” ok, but when “*” and “count(*)” is switched position, then it gives an error?

In SQL, using the table: mysql> select * from sentGifts; +--------+------------+--------+------+---------------------+--------+ | sentID | whenSent | fromID | toID | trytryWhen | ...
4
votes
2answers
4k views

In SQL, what does Group By mean without Count(*), or Sum(), Max(), avg(), …, and what are some uses of it?

In SQL, if we use Group By without Count(*) or Sum(), etc, then the result is as follows: mysql> select * from sentGifts; +--------+------------+--------+------+---------------------+--------+ | ...
1
vote
1answer
871 views

What is a SQL statement that can tally up the counts even including the Zeros? (all in 1 statement)

A SQL statement can give a list of the most popular gifts that are sent in a Social application, all the way to the ones that are sent 1, or 2 times, but it won't include the Zeros. I think the same ...
15
votes
3answers
19k views

count total records after groupBy select

I have a mysql select query that has a groupBy. I want to count all the records after the group by statement. Is there a way for this directly from mysql ? thanks.
0
votes
2answers
2k views

MySQL select using datetime, group by date only

Is is possible to select a datetime field from a MySQL table and group by the date only? I'm trying to output a list of events that happen at multiple times, grouped by the date it happened on. My ...
1
vote
3answers
94 views

mysql query performance help

I have a quite large table storing words contained in email messages mysql> explain t_message_words; +----------------+---------+------+-----+---------+----------------+ | Field | Type ...
0
votes
1answer
241 views

Mysql query different group by

Hello i have a products table that contains normal products and configurable product It has a basic stucture of: id name price configurable ('yes', 'no') id_configuration Normal products have ...
24
votes
5answers
52k views

Why doesn't Oracle SQL allow us to use column aliases in GROUP BY clauses?

This is a situation I'm generally facing while writing SQL queries. I think that writing the whole column (e.g. long case expressions, sum functions with long parameters) instead of aliases in GROUP ...
2
votes
1answer
2k views

Get the count of items in a GroupBy Linq-to-sql Query?

I have a GroupBy that I groups all elements. I can see the items are there in LinqPad but can't find a way to get the count. Here is what I have so far: SurveyResponses.Where( q => q.QuestionId ==...
2
votes
1answer
3k views

SQL statement HAVING MAX(some+thing)=some+thing

I'm having trouble with Microsoft Access 2003, it's complaining about this statement: select cardnr from change where year(date)<2009 group by cardnr having max(time+date) = (time+date) and ...
0
votes
1answer
201 views

Mysql - Grouping the result based on a mathematical operation and SUM() function

I'm having the following two tables... Table : room_type type_id type_name no_of_rooms max_guests rate 1 Type 1 15 2 1254 2 Type 2 ...
1
vote
1answer
3k views

Teradata group by time interval

Can anyonen help with Teradata? I want to create a query that is a standard select count(*) from Table where Column = Something but has a group by time period done by 5 minute time intervals ...
0
votes
3answers
57 views

I don't find the sql request

Here it's my problem I've a list of the following measure : src1 dst2 24th december 2009 src1 dst3 22th december 2009 src1 dst2 18th december 2009 I would like to have just the latest ...
13
votes
3answers
10k views

MYSQL - Group by limit

Is there a simple way to LIMIT the GROUP BY results to the top 2. The following query returns all the results. Using 'LIMIT 2' reduces the overall list to the top 2 entries only. select distinct(...
1
vote
1answer
2k views

sql group by with left join

fail statement:Error: ORA-00979: not a GROUP BY expression select org_division.name , org_department.name , org_surveylog.division_code as divisionCode,org_surveylog.department_code as ...
0
votes
2answers
947 views

MySQL query count multiple values

Consider the following DB table: c p ========= 1 'a' 1 'b' 2 'a' 2 'c' Now, my goal is to retrieve a list of numbers c, for which holds that each number in this list has at least ...
2
votes
3answers
520 views

How to select the record contains MAX(some_field) within GROUP(group by)

SELECT MAX(some_field) FROM table_A GROUP BY another_field This only gets the max value of the 'some_field'; I want to get the whole record which contains the MAX(some_field).
1
vote
1answer
148 views

Is there a name for this type of algorithm?

I have a 2 dimensional array forming a table: [color][number][shape ] ------------------------- [black][10 ][square ] [black][10 ][circle ] [red ][05 ][triangle] [red ][04 ][...
0
votes
2answers
3k views

Calculating average (AVG) and grouping by week on large data set takes too long

I'm getting average prices by week on 7 million rows, it's taking around 30 seconds to get the job done. This is the simple query: SELECT AVG(price) as price, yearWEEK(FROM_UNIXTIME(timelog)) as ...
1
vote
1answer
2k views

SQL query giving “not a GROUP BY expression” error

SELECT SUM(a.Clicks) AS Clicks, SUM(b.NoOfUsers) Users, c.WEEK_NUM, b2.ALL_TASKS FROM (SELECT SUM(CLICK_CNT) AS Clicks, TO_CHAR(RQST_DT,'YYYY-MM-DD') AS DATEE FROM PER_DAY_USAGE GROUP ...
1
vote
2answers
667 views

Want to calculate the sum of the count rendered by group by option

i have a table with the columns such id, tid, companyid, ttype etc.. the id may be same for many companyid but unique within the companyid and tid is always unique and i want to calculate the total ...
3
votes
3answers
3k views

sql - getting the id from a row based on a group by

Table A tableAID tableBID grade Table B tableBID name description Table A links to Table b from the tableBID found in both tables. If I want to find the row in Table A, which has the highest ...
2
votes
3answers
6k views

how to group MySql rows based on month?

I've a table with a datetime (format: 'Y-m-d H:i:s') 'created' field and 'amount' (integer) field in each row. Now I want to find out month wise total 'amount' in last year. How can I do this? EDIT ...
2
votes
3answers
225 views

MySQL GROUP BY with three tables

I have the following tables: posts (post_id, content, etc) comments (comment_id, post_id, content, etc) posts_categories (post_category_id, post_id, category_id) and this query: SELECT `p`.* ...
0
votes
1answer
961 views

Entity Sql Group By problem, please help

help me please with this simple E-sql query: var qStr = "SELECT SqlServer.Month(o.DatePaid) as month, SqlServer.Sum(o.PaidMoney) as PaidMoney FROM XACCModel.OrdersIncomes as o group by SqlServer....
2
votes
3answers
596 views

Get Max() record from table by group

i've got a table like that : Article Number Last Voucher Number Last Voucher Date 0557934 519048 04/02/2005 0557934 519067 04/02/2005 0557934 ...
4
votes
4answers
2k views

SQL Group By Modulo of Row Count

I have the following sample data: Id Name Quantity 1 Red 1 2 Red 3 3 Blue 1 4 Red 1 5 Yellow 3 So for this example, there are a total of 5 Red, ...
1
vote
3answers
442 views

Mysql Groupby and Orderby problem

Here is my data structure alt text http://luvboy.co.cc/images/db.JPG when i try this sql select rec_id, customer_id, dc_number, balance from payments where customer_id='IHS050018' group by ...
3
votes
6answers
6k views

Ordering of month/year pairs in T-SQL query

I am writing a stored procedure for displaying month and year. It is working, but it is not returning the rows in the desired order. ALTER procedure [dbo].[audioblog_getarchivedates] as begin ...
4
votes
1answer
4k views

Is it possible to group rows twice in MySQL?

I have a table like this: someid somestring 1 Hello 1 World 1 Blah 2 World 2 TestA 2 TestB ... Currently I'm grouping by the id and concatenating ...
0
votes
1answer
530 views

How to use the distinct on databases DB4O

the data like this: file1 file2 aaaa milk aaaa red bbbb box bbbb pen cccc rose i want get result like this: file1: aaaa bbbb cccc who can tell me how to do using DB4objects ...
2
votes
1answer
258 views

Better way to do SELECT with GROUP BY

Hi i've wrote a query that works: SELECT `comments`.* FROM `comments` RIGHT JOIN (SELECT MAX( id ) AS id, core_id, topic_id FROM comments GROUP BY core_id, topic_id order by id desc) comm ON comm....
1
vote
2answers
7k views

Linq-To-Objects group by

I'm building a software for timereporting I have a Dictionary<string, Dictionary<string, double>>. The key in the main dictionary is a users name and their value is a dictionary of . I ...
1
vote
3answers
526 views

SQL server query not showing daily date result

I have a simple user production report where daily quotas are tracked. The sql returns a weeks worth of data for all users and for each day it tracks their totals. The problem is if they are out for a ...
19
votes
5answers
98k views

mysql query: SELECT DISTINCT column1, GROUP BY column2

Right now I have the following query: SELECT name, COUNT(name), time, price, ip, SUM(price) FROM tablename WHERE time >= $yesterday AND time <$today GROUP BY name And what I'd like to ...
4
votes
3answers
2k views

Linq-to-SQL: How to shape the data with group by?

I have an example database, it contains tables for Movies, People and Credits. The Movie table contains a Title and an Id. The People table contains a Name and an Id. The Credits table relates ...
9
votes
4answers
3k views

MySQL: filling empty fields with zeroes when using GROUP BY

I've got MySQL table CREATE TABLE cms_webstat ( ID int NOT NULL auto_increment PRIMARY KEY, TIMESTAMP_X timestamp DEFAULT CURRENT_TIMESTAMP, # ... some other fields ... ) which contains ...
0
votes
3answers
1k views

how to count NULL categories in one SQL question

i have a blog application were Post belongsTo Category and Category hasMany Post Post can have a Category or not - in latter case NULL value is present in Post.category_id field. Now i would like to ...
-1
votes
1answer
7k views

LINQ SELECT COUNT(*) AND EmployeeId

I have a table like below: EmployeeId EmployeeName RequestId RequestName EmployeeId RequestId I need to a to assign requests in a sequential fashion(those who has mininum number of requests). Can ...
0
votes
1answer
504 views

sql: Group by x,y,z; return grouped by x,y with lowest f(z)

This is for http://cssfingerprint.com I collect timing stats about how fast the different methods I use perform on different browsers, etc., so that I can optimize the scraping speed. Separately, I ...
1
vote
1answer
1k views

How can I provide values for non-grouped columns in NHibernate?

I have a criteria query: Session.CreateCriteria<Sell043Report>() .SetProjection(.ProjectionList() .Add(LambdaProjection.GroupProperty<Sell043Report>(r => r.location)) .Add(...
0
votes
1answer
6k views

LINQ to SQL, select targets with max date

I ended up with this horrible code below, I can't get a better result now. What is a better way of doing that? It's about this part of my database: EDIT A Patient has a Subscription to multiple ...
2
votes
3answers
3k views

sql select statement with a group by

I have data in 2 tables, and I want to create a report. Table A: tableAID (primary key) name Table B: tableBID (primary key) grade tableAID (foreign key, references Table A) There is much more ...
1
vote
2answers
813 views

Getting random record from database with group by

Hello i have a question on picking random entries from a database. I have 4 tables, products, bids and autobids, and users. Products ------- id 20,21,22,23,24(prime_key) price........... etc...........
0
votes
2answers
221 views

case in group by in a store procedure

is possible do a case in a group by? similar to this: select * from table GROUP BY CASE WHEN @Attivita=0 THEN (RANK() OVER (GROUP BY Nome,AccountID,Matricola DESC)) END thanks
13
votes
1answer
2k views

What are the Ruby equivalent of Python itertools, esp. combinations/permutations/groupby?

Python's itertools module provides a lots of goodies with respect to processing an iterable/iterator by use of generators. For example, permutations(range(3)) --> 012 021 102 120 201 210 ...
3
votes
2answers
2k views

Django: Group by?

I'm looking for something like the following: previous_invoices = Invoice.objects.filter(is_open=False) .order_by('-created') ....
1
vote
1answer
1k views

Group by with ActiveRecord in Rails

I have a the following table with rows: =============================================== id| name | group1 | group2 | group3 | group4 | =============================================== 1 | Bob | 1 ...
1
vote
1answer
1k views

SQL COUNT of COUNT

I have some data I am querying. The table is composed of two columns - a unique ID, and a value. I would like to count the number of times each unique value appears (which can easily be done with a ...
905
votes
2answers
875k views

Using group by on multiple columns

I understand the point of GROUP BY x But how does GROUP BY x, y work, and what does it mean?