By l--''''''---------''''''''''''

2010-03-10 23:11:23 8 Comments

I understand the point of GROUP BY x

But how does GROUP BY x, y work, and what does it mean?


@S. Mayol 2016-12-15 22:14:31

The GROUP BY clause is used in conjunction with the aggregate functions to group the result-set by one or more columns. e.g.:

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

Remember this order:

1) SELECT (is used to select data from a database)

2) FROM (clause is used to list the tables)

3) WHERE (clause is used to filter records)

4) GROUP BY (clause can be used in a SELECT statement to collect data across multiple records and group the results by one or more columns)

5) HAVING (clause is used in combination with the GROUP BY clause to restrict the groups of returned rows to only those whose the condition is TRUE)

6) ORDER BY (keyword is used to sort the result-set)

You can use all of these if you are using aggregate functions, and this is the order that they must be set, otherwise you can get an error.

Aggregate Functions are:

MIN returns the smallest value in a given column

SUM returns the sum of the numeric values in a given column

AVG returns the average value of a given column

COUNT returns the total number of values in a given column

COUNT(*) returns the number of rows in a table

@Chaitanya Bapat 2017-11-17 22:13:20

but where do we put the 2 columns, how to aggregate based on 2/more columns is the question

@S. Mayol 2017-11-22 18:25:35

Hi Chaitanya, I do not know if this is what you are asking but let me give you just few example. If you have a product table you use aggregate functions this way, below are two scenarios: SELECT AVG(instock) FROM products; This will calculate the average units in stock of the products table. Now you want to calculate units in stock by product category, you have to use the AVG function with the GROUP BY clause like this: SELECT categoryId, AVG(instock) FROM products GROUP BY categoryId;

@MahNas92 2019-05-20 10:22:05

This doesn't even remotely answer the question... The question here is how to acheive "chained grouping" of "subject" and "semester" at the same time, as explained in the given example...

@Smashery 2010-03-10 23:24:01

Group By X means put all those with the same value for X in the one group.

Group By X, Y means put all those with the same values for both X and Y in the one group.

To illustrate using an example, let's say we have the following table, to do with who is attending what subject at a university:

Table: Subject_Selection

Subject   Semester   Attendee
ITB001    1          John
ITB001    1          Bob
ITB001    1          Mickey
ITB001    2          Jenny
ITB001    2          James
MKB114    1          John
MKB114    1          Erica

When you use a group by on the subject column only; say:

select Subject, Count(*)
from Subject_Selection
group by Subject

You will get something like:

Subject    Count
ITB001     5
MKB114     2

...because there are 5 entries for ITB001, and 2 for MKB114

If we were to group by two columns:

select Subject, Semester, Count(*)
from Subject_Selection
group by Subject, Semester

we would get this:

Subject    Semester   Count
ITB001     1          3
ITB001     2          2
MKB114     1          2

This is because, when we group by two columns, it is saying "Group them so that all of those with the same Subject and Semester are in the same group, and then calculate all the aggregate functions (Count, Sum, Average, etc.) for each of those groups". In this example, this is demonstrated by the fact that, when we count them, there are three people doing ITB001 in semester 1, and two doing it in semester 2. Both of the people doing MKB114 are in semester 1, so there is no row for semester 2 (no data fits into the group "MKB114, Semester 2")

Hopefully that makes sense.

@tumchaaditya 2014-09-26 18:08:35

@Smashery: So does this also mean that GROUP BY A,B is same as GROUP BY B,A?

@Smashery 2014-09-29 00:10:44

Yes, it does. I can't say for certain whether they are as efficient as each other, but they will give the same result, yes.

@Dwza 2015-03-04 15:06:04

May here should be added that there is a difference between GROUP BY a, b and GROUP BY a AND b since the second one only lists grouped items with exactly the same content and no "undergroups". In this case the output would be same like the first one.

@user2441441 2015-09-29 21:07:08

I would like to add that the order in which you group by the columns does not matter. In the above example group by Semester, Subject would have given the same result

@cokedude 2017-11-29 00:07:49

@Smashery how does Group By A, B, C, D behave? It seems to be distributive when I test it out in my database. The similar AB, AC, AD seemed to get grouped together.

@fanny 2018-10-03 09:14:37

well, group by a, b and group by b, a do NOT return the same result - the rows are displayed in a different order

@coffman21 2019-08-26 09:21:10

@fanny in terms of relations results are the same, order is irrelevant here.

Related Questions

Sponsored Content

7 Answered Questions

16 Answered Questions

[SOLVED] Select first row in each GROUP BY group?

48 Answered Questions

[SOLVED] How do I import an SQL file using the command line in MySQL?

41 Answered Questions

10 Answered Questions

[SOLVED] Group by in LINQ

  • 2011-09-06 19:44:20
  • test123
  • 1320362 View
  • 1069 Score
  • 10 Answer
  • Tags:   c# linq group-by

27 Answered Questions

[SOLVED] Retrieving the last record in each group - MySQL

20 Answered Questions

[SOLVED] Get top 1 row of each group

14 Answered Questions

[SOLVED] Group By Multiple Columns

11 Answered Questions

[SOLVED] Can I concatenate multiple MySQL rows into one field?

Sponsored Content