|
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!
|
Vry nice
