The Tricky.net

GROUP BY vs ORDER BY

Lately, when I was designing a query for a website, I was wondering about the exact difference between group by and order by in a query. The easiest explanation is that order by is doing the sorting of a table and the group by clause is used for aggregation of a field. Ok, this is for the theory, let's see an example:


The following table contains the items bought by several people last week:


cust_id item total price
1 balloon 1
2 apple 3
1 apple 4
1 pillow 25
3 plastic bag 1


Order By

Let's see the following query:

SELECT * FROM shopping ORDER BY total_price


The output will be:



cust_id item total price
1 balloon 1
3 plastic bag
1
2 apple 3
1 apple 4
1 pillow 25


as you can see the fiels have been ordered by the price. The default order is ascending. If you want to specify how the data is ordered, write either ASC or DESC at the end of your query.


Example:

SELECT * FROM shopping ORDER BY total_price DESC
will give the same table, but starting with the pillow.


Group By


Now we are going to use the group by statement. The group by statement, as said before, is especially useful for aggregating, meaning to apply some function. Let's see an example:


SELECT cust_id, SUM(total_price) FROM shopping GROUP BY cust_id


This query returns the total amount of money spent by each customer during all their shoppings. The table returned looks like this:


cust_id SUM(total_price)
1 30
2 3
3 1


The way you have to understand the query is that we compute the sum of all amounts for each customer. This is expressed by the GROUP BY cust_id. Now, if we would try to do this for each product. This would correspond to the total money gained per product. The query looks like this:


SELECT item, SUM(total_price) FROM shopping GROUP BY item


This query returns the following table:



item SUM(total_price)
apple 7
balloon 1
pillow 25
plastic bag 1


That's already it!

Comments
Search RSS
seenu (180.151.61.xxx) 12-07-2013 11:54:58

Thanks for the clear explanation. Easy to understand
Great!
mityaz (109.254.136.xxx) 24-06-2013 12:17:52

Thanks!
thanks
himan (115.252.183.xxx) 22-05-2013 08:12:30

really awesome explanation
Anonymous (119.82.87.xxx) 13-05-2013 10:30:44

Great stuff
pramod (111.118.250.xxx) 11-04-2013 07:58:44

very useful for us
Anonymous (190.80.8.xxx) 07-04-2013 15:42:24

Wonderful job! Thanks a million!
Usefull
Prusothaman (124.30.96.xxx) 25-10-2012 08:20:01

Its very usefull fresher who going to know basics

Thanks
Thanks
john (117.193.152.xxx) 05-10-2012 04:56:29

Thanks for the clear explanation. Easy to understand
santhi (49.204.151.xxx) 26-09-2012 13:45:17

Thanks its really nice
gud
p (14.98.156.xxx) 18-09-2012 11:20:40

wonderful explanation. thanx bud.
Samy (14.99.185.xxx) 07-09-2012 17:30:14

Really too good explination .... My confusion got cleared in 5mins....
THanks
Arbaaz (1.186.127.xxx) 31-08-2012 18:42:30

Finally , a site which cleared it out !
sql
Nitin niraj (122.169.5.xxx) 19-07-2012 13:03:19

Thanks very nice and simple explanation
Nice Example (149.111.176.xxx) 28-06-2012 15:46:46

vr (117.195.115.xxx) 09-02-2012 16:25:23

very good explanation!!! !!
Consultas con group by
joseb (201.58.230.xxx) 03-02-2012 17:06:39

Como hago con Php Y Mysql para que la consulta se vea así.
manzanas | globo |
almohada | bolsa..
7 | 1 | 25 | 1

Gracias.
jeyan
jeyan (141.0.9.xxx) 01-02-2012 19:22:16

excellent explanation...its easy to understand.
DP
Durgesh (182.71.26.xxx) 15-12-2011 11:29:44

Great explaination dude
Thanx
dhoti (68.46.185.xxx) 04-01-2012 22:50:13

like it. very clear explanation.
deepa (115.112.231.xxx) 14-01-2012 05:45:42

good explanation
deepa (115.112.231.xxx) 14-01-2012 05:46:17

good explanation
orderby
ASWINI.G (49.200.209.xxx) 19-01-2012 11:57:23

Thanks its really good explanation.
Anonymous (122.164.174.xxx) 24-02-2012 08:48:55

nice........
drisya (121.246.82.xxx) 16-03-2012 11:45:21

good
sanjay singh (203.62.174.xxx) 28-11-2011 08:51:31

Given example is really helpfull.
Thanks
good
farooq (119.151.20.xxx) 21-09-2011 11:42:12

Good Work
Dinesh (123.108.229.xxx) 09-09-2011 10:40:39

Well explained please to keep posting more such examples.Very explanatory.
Thank
You
Well explained
john (86.178.128.xxx) 05-10-2011 16:17:45

sexy
Anonymous (203.99.197.xxx) 19-07-2011 15:38:44

superb
Very helpful
Anonymous (115.184.69.xxx) 12-07-2011 09:46:00

It is very explainative.. Thanks a lot..
Anonymous (144.188.24.xxx) 09-06-2011 08:45:08

Vry nice
Simply great
gauravpreet (122.173.75.xxx) 05-05-2011 08:51:01

Very Nicely explained
satish (183.82.102.xxx) 28-04-2011 13:35:21

Thanks it helped a lot to understand very practically and simple explanation.

Anonymous (155.91.28.xxx) 09-03-2011 19:19:46

Group by & order by together
Hanif (109.162.196.xxx) 19-02-2011 06:59:41

I tried to use group by and order by together but i do not know why after
running the query the order by does not sort data.

SELECT Customers.Company,
calls.ID, count(Customers.Company) as 'ocount'
FROM Customers INNER JOIN calls
ON Customers.ID = calls.ID
where code is not null
GROUP by
Customers.Company,calls.ID
order by count(Customers.Company) desc
Anonymous (121.242.47.xxx) 30-08-2011 10:08:26

I am having the same problem
Sander (SAdministrator) 30-08-2011 20:16:13

Could you try changing "order by count(Customers.Company) desc" to
"order by ocount desc"?
Anonymous (59.99.162.xxx) 15-02-2011 07:46:52

thx
lily (110.184.123.xxx) 28-04-2011 23:51:43

Very nice.It is very Helpful
Ashwini (49.138.6.xxx) 26-01-2011 08:09:02

Thanks For this simplification i was really confused and went through many sites
for this sites for this but u made it very simple for me.. Thankssssss
Anonymous (141.209.34.xxx) 27-10-2010 23:55:30

Thanks a lot.I was bit confused before but not any more..tnkx again
simple and lucid
srikanth (119.235.54.xxx) 11-10-2010 09:00:02

good work dude, keep it up
simran (117.199.167.xxx) 02-09-2010 13:49:33

realy very simple and good method for learning the difference between the two.
hope it will help me.
Anonymous (212.252.168.xxx) 01-09-2010 12:02:36

When you group by a column, is the resultset automatically ordered by that
column as well?
Sander (SAdministrator) 01-09-2010 15:33:39

Normally it won't. If the order is important you should use (for example)
"ORDER BY `item` ASC", which guarantees the sorting. This would change
the example query to:
SELECT item, SUM(total_price) FROM shopping GROUP BY item
ORDER BY item ASC
awesome
deviprasad (203.99.193.xxx) 16-08-2010 07:40:52

awesome dude....
clear cut explanation..
Good article
critical (131.182.85.xxx) 25-02-2010 21:25:40

Very clear tutorial. Please do another one for JOINS.

Thanks!
YEs, simple
Vikram (15.211.169.xxx) 13-02-2010 03:44:45

Really simple work
Bharanikumar (122.164.35.xxx) 05-01-2010 04:30:52

Really one simple example, after long i clearly understand ,

Can u write
somthing like this example for

LEFT JOIN AND LEFT OUTER JOIN,

Diff ,Becoz i
found lot and lot threads, but u see am not much satisfied like this example,,,
Anonymous (128.189.245.xxx) 15-11-2009 01:16:40

Very nice and clear explanation! thanks!
Good
sai (123.176.47.xxx) 18-08-2010 13:47:17

Very nice.It is very Helpful
nita (122.169.127.xxx) 26-08-2011 08:04:18

thanks its very nice n clear 4 understanding
Anonymous (110.234.135.xxx) 18-03-2013 07:42:56

Good Example...
Only registered users can write comments!

3.23 Copyright (C) 2007 Alain Georgette / Copyright (C) 2006 Frantisek Hliva. All rights reserved."