GROUP BY

Used to group the output of the result set

 

Often required by aggregate functions, e.g.

  • sum()
  • avg()
  • min()
  • max()
  • count()

 

Consider the orders table:

mysql> select * from orders;
+---------+--------+----------+-----+------+
| OrderID | CustID | Part     | Qty | Cost |
+---------+--------+----------+-----+------+
|       1 |      3 | CPU      |   2 |  975 |
|       2 |      1 | RAM      |   3 |  433 |
|       3 |      2 | SSD      |   2 |  768 |
|       4 |      3 | Monitor  |   3 |  250 |
|       5 |      3 | MoBo     |   4 |  666 |
|       6 |      1 | Case     |   2 |  150 |
|       7 |      2 | HDD      |  10 |  325 |
|       8 |      3 | Keyboard |   5 |  250 |
+---------+--------+----------+-----+------+
8 rows in set (0.00 sec)

 

 

If we wanted to find out the total expenditure of each customer, we would use the sum() function to add all the individual order quantities by the cost and use GROUP BY to conveniently group each customers total:

mysql> select CustID, sum(Qty * Cost) AS total from orders group by CustID;
+--------+-------+
| CustID | total |
+--------+-------+
|      1 |  1599 |
|      2 |  4786 |
|      3 |  5614 |
+--------+-------+
3 rows in set (0.00 sec)

Leave a Reply