GROUP BY 子句与SELECT语句配合使用,将相同的数据分为几组。该GROUP BY子句在SELECT语句中的WHERE子句之后,并在ORDER BY子句之前。
以下代码块显示了GROUP 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 | Learnfk | 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 | | Learnfk | 10000.00 | | Ramesh | 2000.00 | +----------+-------------+
现在,让无涯教程看一个表,其中CUSTOMERS表具有以下具有重复名称的记录-
链接:https://www.learnfk.comhttps://www.learnfk.com/sql/sql-group-by.html
来源:LearnFk无涯教程网
+----+----------+-----+-----------+----------+ | 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 | Learnfk | 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 | | Learnfk | 10000.00 | | Ramesh | 3500.00 | +---------+-------------+
祝学习愉快!(内容编辑有误?请选中要编辑内容 -> 右键 -> 修改 -> 提交!)