Page 1 of 1
difference between GROUP BY and ORDER BY?
Posted: Thu Feb 26, 2004 3:39 pm
by Unipus
The MySQL manual is a mess, and I can't find a straightforward explanation anywhere. Can anyone tell me?
Posted: Thu Feb 26, 2004 3:56 pm
by infolock
well, best way to explain it is with an example :
say you have 10 types of shoes you sale on your site, and you are keeping track of them in a table. you are keeping track of the quanity sold, the price, name, number of each brand sold, and shoe size.
now, you want to be able to get all the same type of shoes together and get an overall count in your inventory.
in this case, you would use a GROUP BY declaration in your mysql syntax, telling it to GROUP BY statement. Which basically means that it will find every entry in your table that has the same value in the Shoe Type field, and group them together ( all nike's together, all reebok, etc )..
now you want to be able to find the shoe you sale the most, ranking for the most popular to the least. in this case, you would use ORDER BY shoes_bought. You can either do DESC for descending order, or ASC for ascending.
the rest, you are gonna have to look up. best place to start is google..
in the search box, just type MySQL Order By
hope this helps.
Posted: Thu Feb 26, 2004 4:02 pm
by Weirdan
Suppose you have the following table:
Code: Select all
create table tst(orderid int autoincrement, purchasedate timestamp,amount float);
insert into tst
values(null,now()-interval 2 month,rand()*1000),
(null,now()-interval 2 month,rand()*1000),
(null,now()-interval 2 month,rand()*1000),
(null,now()-interval 2 month,rand()*1000),
(null,now()-interval 1 month,rand()*1000),
(null,now()-interval 1 month,rand()*1000),
(null,now()-interval 1 month,rand()*1000),
(null,now(),rand()*1000),
(null,now(),rand()*1000),
(null,now(),rand()*1000);
'SELECT * from tst order by month(purchasedate)' will give you 10 rows ordered by purchasedate. 'SELECT * from tst group by month(purchasedate)' will give you three rows (one for each month).
'SELECT month(purchasedate),count(*),sum(amount) from tst group by month(purchasedate)' will give you three rows of totals for each month (count of orders per month and total amount of sales per month). GROUP BY is useful to compute such a stats.
Posted: Thu Feb 26, 2004 4:27 pm
by Unipus
hmm, gotcha. Seems to be doing weird things with my query but I'll see what I can do with it for a bit.