SQL - GROUP BY 子句

简述

SQLGROUP BY子句与 SELECT 语句配合使用，将相同的数据分组。此 GROUP BY 子句在 SELECT 语句中的 WHERE 子句之后并在 ORDER BY 子句之前。
句法

GROUP BY 子句的基本语法显示在以下代码块中。GROUP BY 子句必须跟在 WHERE 子句中的条件之后，并且如果使用了 ORDER BY 子句，则必须在 ORDER BY 子句之前。
```
SELECT column1, column2
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2
ORDER BY column1, column2
```

例子

考虑 CUSTOMERS 表有以下记录 -


+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

如果您想知道每个客户的工资总额，那么 GROUP BY 查询将如下所示。


SQL> SELECT NAME, SUM(SALARY) FROM CUSTOMERS
   GROUP BY NAME;

这将产生以下结果 -


+----------+-------------+
| NAME     | SUM(SALARY) |
+----------+-------------+
| Chaitali |     6500.00 |
| Hardik   |     8500.00 |
| kaushik  |     2000.00 |
| Khilan   |     1500.00 |
| Komal    |     4500.00 |
| Muffy    |    10000.00 |
| Ramesh   |     2000.00 |
+----------+-------------+

现在，让我们看一个表，其中 CUSTOMERS 表具有以下重复名称的记录 -


+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Ramesh   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | kaushik  |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

现在再一次，如果您想知道每个客户的工资总额，那么 GROUP BY 查询将如下所示 -


SQL> SELECT NAME, SUM(SALARY) FROM CUSTOMERS
   GROUP BY NAME;

这将产生以下结果 -


+---------+-------------+
| NAME    | SUM(SALARY) |
+---------+-------------+
| Hardik  |     8500.00 |
| kaushik |     8500.00 |
| Komal   |     4500.00 |
| Muffy   |    10000.00 |
| Ramesh  |     3500.00 |
+---------+-------------+

SQL - GROUP BY 子句

简述

句法

例子