SQL GROUP BY

SQL GROUP BY should be added with all aggregate functions and it s used in conjunction with the aggregate functions to group the result-set by one or more columns.

Table – Users

+—–+——+
| uid | name |
+—–+——+
|   1 | AAAA |
|   2 | BBBB |
|   3 | CCCC |
|   4 | DDDD |
|   5 | EEEE |
+—–+——+

Table – Orders

+—–+—–+——-+
| oid | uid | price |
+—–+—–+——-+
|   1 |   1 | 60    |
|   2 |   2 | 30    |
|   3 |   2 | 60    |
|   4 |   1 | 70    |
|   5 |   2 | 80    |
|   6 |   1 | 50    |
+—–+—–+——-+

Example

mysql> SELECT SUM(o.price) AS total,u.name FROM users AS u JOIN orders AS o ON u.uid=o.uid GROUP BY o.uid;;
+——-+——+
| total | name |
+——-+——+
| 180 | AAAA |
| 170 | BBBB |
+——-+——+
2 rows in set (0.00 sec)


Share