difference between GROUP BY and ORDER BY?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Unipus
Forum Contributor
Posts: 409
Joined: Tue Aug 26, 2003 2:06 pm
Location: Los Angeles, CA

difference between GROUP BY and ORDER BY?

Post by Unipus »

The MySQL manual is a mess, and I can't find a straightforward explanation anywhere. Can anyone tell me?
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post 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.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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.
Unipus
Forum Contributor
Posts: 409
Joined: Tue Aug 26, 2003 2:06 pm
Location: Los Angeles, CA

Post 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.
Post Reply