SQL GROUP BY 用法

最新推荐文章于 2024-06-29 12:38:57 发布
最新推荐文章于 2024-06-29 12:38:57 发布
阅读量414 收藏
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/songhao8080/article/details/103669878
版权
Python
#1、首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的 #2、分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等 #3、为何要分组呢？ 取每个部门的最高工资 取每个部门的员工数 取男人数和女人数 小窍门：‘每’这个字后面的字段，就是我们分组的依据 #4、大前提： 可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需要借助于聚合函数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
#1、首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的
 
#2、分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等
 
#3、为何要分组呢？
    取每个部门的最高工资
    取每个部门的员工数
    取男人数和女人数
 
小窍门：‘每’这个字后面的字段，就是我们分组的依据
 
#4、大前提：
    可以按照任意字段分组，但是分组完毕后，比如 group by post，只能查看 post字段，如果想查看组内信息，需要借助于聚合函数
 

GROUP BY 语法

Python
SELECT <column_list> FROM < table name > WHERE <condition>GROUP BY <columns> [HAVING] <condition>;
1
2
3
4
5
SELECT < column_list >
FROM < table name >
WHERE < condition > GROUP BY < columns >
[ HAVING ] < condition > ;
 

数据库结构

SQL GROUP BY with COUNT() function

Python
SELECT department_id "Department Code", COUNT(*) "No of Employees" FROM employees GROUP BY department_id; ### out Department Code No of Employees --------------- --------------- 100 6 30 6 1 90 3 20 2 70 1 110 2 50 45 80 34 40 1 60 5 10 1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT department _id "Department Code" ,
COUNT ( * ) "No of Employees"
FROM employees
GROUP BY department_id ;
 
### out
Department Code No of Employees
-- -- -- -- -- -- -- - -- -- -- -- -- -- -- -
             100                6
             30                6
                               1
             90                3
             20                2
             70                1
             110                2
             50                45
             80                34
             40                1
             60                5
             10                1
 

SQL GROUP BY with SUM() function

Python
SELECT department_id, SUM(salary) FROM employees GROUP BY department_id; # out DEPARTMENT_ID SUM(SALARY) ------------- ----------- 100 51608 30 24900 7000 90 58000 20 19000 70 10000 110 20308 50 156400 80 304500 40 6500 60 28800 10 4400
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT department_id , SUM ( salary )
FROM    employees
GROUP BY   department_id ;
# out
DEPARTMENT_ID SUM ( SALARY )
-- -- -- -- -- -- - -- -- -- -- -- -
           100        51608
           30        24900
                     7000
           90        58000
           20        19000
           70        10000
           110        20308
           50        156400
           80        304500
           40          6500
           60        28800
           10          4400
 

SQL GROUP BY with COUNT() and SUM() function

Python
SELECT department_id "Department Code", COUNT(*) "No of Employees", SUM(salary) "Total Salary" FROM employees GROUP BY department_id; # out SQL Tutorial SQL GROUP BY clause Last update on April 14 2018 06:14:58 (UTC/GMT +8 hours) GROUP BY clause The usage of SQL GROUP BY clause is, to divide the rows in a table into smaller groups. The GROUP BY clause is used with the SQL SELECT statement. The grouping can happen after retrieves the rows from a table. When some rows are retrieved from a grouped result against some condition, that is possible with HAVING clause. The GROUP BY clause is used with the SELECT statement to make a group of rows based on the values of a specific column or expression. The SQL AGGREGATE function can be used to get summary information for every group and these are applied to an individual group. The WHERE clause is used to retrieve rows based on a certain condition, but it can not be applied to grouped result. In an SQL statement, suppose you are using GROUP BY, if required you can use HAVING instead of WHERE, after GROUP BY. Syntax: SELECT <column_list> FROM < table name > WHERE <condition>GROUP BY <columns> [HAVING] <condition>; Parameters: Name Description table_name Name of the table. column_list Name of the columns of the table. columns Name of the columns which will participate in grouping.. Pictorial Presentation of Groups of Data SQL Groups of Data Using GROUP BY with Aggregate Functions - The power of aggregate functions is greater when combined with the GROUP BY clause. - The GROUP BY clause is rarely used without an aggregate function. SQL GROUP BY with COUNT() function The following query displays number of employees work in each department. Sample table: employees SQL Code: SELECT department_id "Department Code", COUNT(*) "No of Employees" FROM employees GROUP BY department_id; Copy Sample Output: Department Code No of Employees --------------- --------------- 100 6 30 6 1 90 3 20 2 70 1 110 2 50 45 80 34 40 1 60 5 10 1 SQL GROUP BY with SUM() function The following query displays total salary paid to employees work in each department. Sample table: employees SQL Code: SELECT department_id, SUM(salary) FROM employees GROUP BY department_id; Copy Sample Output: DEPARTMENT_ID SUM(SALARY) ------------- ----------- 100 51608 30 24900 7000 90 58000 20 19000 70 10000 110 20308 50 156400 80 304500 40 6500 60 28800 10 4400 SQL GROUP BY with COUNT() and SUM() function The following query displays number of employees, total salary paid to employees work in each department. Sample table: employees SQL Code: SELECT department_id "Department Code", COUNT(*) "No of Employees", SUM(salary) "Total Salary" FROM employees GROUP BY department_id; Copy Sample Output: Department Code No of Employees Total Salary --------------- --------------- ------------ 100 6 51608 30 6 24900 1 7000 90 3 58000 20 2 19000 70 1 10000 110 2 20308 50 45 156400 80 34 304500 40 1 6500 60 5 28800 10 1 4400
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
SELECT department _id "Department Code" ,
COUNT ( * ) "No of Employees" ,
SUM ( salary ) "Total Salary"
FROM    employees
GROUP BY   department_id ;
# out
 
SQL Tutorial
SQL GROUP BY clause
Last update on April 14 2018 06 : 14 : 58 ( UTC / GMT + 8 hours )
GROUP BY clause
The usage of SQL GROUP BY clause is , to divide the rows in a table into smaller groups .
 
The GROUP BY clause is used with the SQL SELECT statement .
 
The grouping can happen after retrieves the rows from a table .
 
When some rows are retrieved from a grouped result against some condition , that is possible with HAVING clause .
 
The GROUP BY clause is used with the SELECT statement to make a group of rows based on the values of a specific column or expression . The SQL AGGREGATE function can be used to get summary information for every group and these are applied to an individual group .
 
The WHERE clause is used to retrieve rows based on a certain condition , but it can not be applied to grouped result .
 
In an SQL statement , suppose you are using GROUP BY , if required you can use HAVING instead of WHERE , after GROUP BY .
 
Syntax :
 
SELECT < column_list >
FROM < table name >
WHERE < condition > GROUP BY < columns >
[ HAVING ] < condition > ;
Parameters :
 
Name     Description
table_name   Name of the table .
column_list Name of the columns of the table .
columns Name of the columns which will participate in grouping . .
Pictorial Presentation of Groups of Data
 
SQL Groups of Data
Using GROUP BY with Aggregate Functions
- The power of aggregate functions is greater when combined with the GROUP BY clause .
- The GROUP BY clause is rarely used without an aggregate function .
 
SQL GROUP BY with COUNT ( ) function
The following query displays number of employees work in each department .
 
Sample table : employees
 
 
SQL Code :
 
SELECT department _id "Department Code" ,
COUNT ( * ) "No of Employees"
FROM employees
GROUP BY department_id ;
Copy
Sample Output :
 
Department Code No of Employees
-- -- -- -- -- -- -- - -- -- -- -- -- -- -- -
             100                6
             30                6
                               1
             90                3
             20                2
             70                1
             110                2
             50                45
             80                34
             40                1
             60                5
             10                1
SQL GROUP BY with SUM ( ) function
The following query displays total salary paid to employees work in each department .
 
Sample table : employees
 
 
 
SQL Code :
 
SELECT department_id , SUM ( salary )
FROM    employees
GROUP BY   department_id ;
Copy
Sample Output :
 
DEPARTMENT_ID SUM ( SALARY )
-- -- -- -- -- -- - -- -- -- -- -- -
           100        51608
           30        24900
                     7000
           90        58000
           20        19000
           70        10000
           110        20308
           50        156400
           80        304500
           40          6500
           60        28800
           10          4400
SQL GROUP BY with COUNT ( ) and SUM ( ) function
The following query displays number of employees , total salary paid to employees work in each department .
 
Sample table : employees
 
 
 
SQL Code :
 
SELECT department _id "Department Code" ,
COUNT ( * ) "No of Employees" ,
SUM ( salary ) "Total Salary"
FROM    employees
GROUP BY   department_id ;
Copy
Sample Output :
 
Department Code No of Employees Total Salary
-- -- -- -- -- -- -- - -- -- -- -- -- -- -- - -- -- -- -- -- --
             100                6          51608
             30                6          24900
                               1          7000
             90                3          58000
             20                2          19000
             70                1          10000
             110                2          20308
             50                45        156400
             80                34        304500
             40                1          6500
             60                5          28800
             10                1          4400
 

SQL GROUP BY on more than one columns

Python
SELECT department_id "Department Code", job_id, SUM(salary) "Total Salary" FROM employees GROUP BY department_id,job_id; # Out; Department Code JOB_ID Total Salary --------------- ---------- ------------ 110 AC_ACCOUNT 8300 90 AD_VP 34000 50 ST_CLERK 55700 80 SA_REP 243500 50 ST_MAN 36400 80 SA_MAN 61000 110 AC_MGR 12008 90 AD_PRES 24000 60 IT_PROG 28800 100 FI_MGR 12008 30 PU_CLERK 13900 50 SH_CLERK 64300 20 MK_MAN 13000 100 FI_ACCOUNT 39600 SA_REP 7000 70 PR_REP 10000 30 PU_MAN 11000 10 AD_ASST 4400 20 MK_REP 6000 40 HR_REP 6500
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
SELECT department _id "Department Code" , job_id ,
SUM ( salary ) "Total Salary"
FROM    employees
GROUP BY   department_id , job_id ;
 
# Out;
Department Code JOB_ID     Total Salary
-- -- -- -- -- -- -- - -- -- -- -- -- -- -- -- -- -- --
             110 AC _ACCOUNT          8300
             90 AD _VP              34000
             50 ST _CLERK            55700
             80 SA _REP            243500
             50 ST _MAN              36400
             80 SA _MAN              61000
             110 AC _MGR              12008
             90 AD _PRES            24000
             60 IT _PROG            28800
             100 FI _MGR              12008
             30 PU _CLERK            13900
             50 SH _CLERK            64300
             20 MK _MAN              13000
             100 FI _ACCOUNT          39600
                 SA _REP              7000
             70 PR _REP              10000
             30 PU _MAN              11000
             10 AD _ASST              4400
             20 MK _REP              6000
             40 HR _REP              6500
 

SQL GROUP BY with WHERE clause

Python
SELECT department_id "Department Code", SUM(salary) "Total Salary" FROM employees WHERE MANAGER_ID = 103 GROUP BY department_id; #------------------------------- Department Code Total Salary --------------- ------------ 60 19800
1
2
3
4
5
6
7
8
9
10
SELECT department _id "Department Code" ,
SUM ( salary ) "Total Salary"
FROM    employees
WHERE MANAGER_ID = 103
GROUP BY   department_id ;
#-------------------------------
Department Code Total Salary
-- -- -- -- -- -- -- - -- -- -- -- -- --
             60          19800
 

SQL GROUP BY with HAVING clause

Python
SELECT department_id, count(*) "No. of Employee" FROM employees GROUP BY department_id HAVING count(*)>2; # OUT DEPARTMENT_ID No. of Employee ------------- --------------- 100 6 30 6 90 3 50 45 80 34 60 5
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT department_id , count ( * ) "No. of Employee"
FROM employees
GROUP BY   department_id
HAVING count ( * ) > 2 ;
# OUT
DEPARTMENT_ID No . of Employee
-- -- -- -- -- -- - -- -- -- -- -- -- -- -
           100                6
           30                6
           90                3
           50                45
           80                34
           60                5
 
 



  • zeropython 微信公众号 5868037 QQ号 5868037@qq.com QQ邮箱
songhao8080
关注
SQL中Group By的使用
10-02 6001
转：http://www.cnblogs.com/rainman/archive/2013/05/01/3053703.html#m5 1、概述 “Group By”从字面意义上理解就是根据“By”指定的规则对数据进行分组，所谓的分组就是将一个“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理。 2、原始表 3、简单Group By ...
详解SQL中Group By的用法
09-10
在SQL中，`GROUP BY`语句是数据分析和报表生成的核心工具之一，它允许我们根据一个或多个列的值对数据进行分组，并且通常与聚合函数一起使用，以对每个组进行计算。以下是关于`GROUP BY`用法的详细说明： 1. **基本...
T-SQL 高级应用之数据汇总（聚合函数之2 group by） .
SelfImprovement
10-28 3850
1、概述2、原始表3、简单Group By4、Group By 和 Order By5、Group By中Select指定的字段限制6、Group By All7、Group By与聚合函数8、Having与Where的区别9、Compute 和 Compute By 1、概述 “Group By”从字面意义上理解就是根据“By”指定的规则对数据进行分组，所谓的分组就是将一个“数据集”划分
SQL中group by详解
最新发布
zhanchulan的博客
06-29 694
数据一致性：分组依据的列应该具有明确的语义和数据一致性。如果列中的数据存在不一致或模糊的情况，可能会导致分组结果不符合预期。聚合函数的使用：确保对分组后的列使用正确的聚合函数来获取有意义的结果。复杂逻辑：当分组逻辑较为复杂时，可能需要仔细考虑如何正确构建查询以获得准确的结果。理解业务需求：清楚地了解业务需求和数据的特点，以确定正确的分组方式和聚合计算。时可能存在细微的差异，特别是在处理空值、边缘情况和扩展功能方面。对数据进行分组，然后计算每个组（即每个学生）的成绩平均值。例如，假设有一个包含学生成绩的表。
group by 的用法
山顶的小树苗
02-15 548
Group By语句最常见的用法了，Group By + [分组字段](可以有多个)。在执行了这个操作以后，数据集将根据分组字段的值将一个数据集划分成各个不同的小组。比如有如下数据集，其中水果名称(FruitName)和出产国家(ProductPlace)为联合主键： FruitName ProductPlace Price
group by的用法
niaixiao1987的博客
01-08 1107
GROUP BY 是 SQL 中的一个子句，用于将多行数据按照一个或多个列进行分组。通常与聚合函数（如 COUNT(), SUM(), AVG() 等）一起使用，以对每个分组进行汇总或计算。这些是 GROUP BY 的基本用法。当然，还有许多高级用法和与其他 SQL 功能（如 JOIN）的结合使用。与 WHERE 子句不同，HAVING 子句用于过滤分组结果。3. 使用 HAVING 子句进行过滤。1. 基本的 GROUP BY 示例。2. 使用多个列进行分组。4. 与聚合函数结合使用。
总结下sqlserver group by 的用法
09-10
在SQL Server中，`GROUP BY`语句是一个用于聚合数据的关键子句，它允许我们将数据分组，并对每个组执行聚合函数（如`COUNT`, `SUM`, `AVG`, `MAX`, `MIN`等）。在数据库查询中，当你需要根据某个或多个列的值对数据...
浅析SQL语句中GROUP BY的用法
12-15
GROUP BY 是分组查询, 一般 GROUP BY 是和 聚合函数配合使用,你可以想想 你用了GROUP BY 按 ITEM.ITEMNUM 这个字段分组,那其他字段内容不同,变成一对多又改如何显示呢,比如下面所示 A B 1 abc 1 bcd 1 asdfg ...
My SQL group by取同组第一条
01-20
除了上述方法，还可以考虑根据实际业务需求调整SQL语句，避免使用子查询和`LIMIT`。例如，你可以直接通过`JOIN`或者`IN`子句来达到同样的效果。此外，了解并适应MySQL的新SQL模式是至关重要的，因为这有助于写出更...
GROUP BY用法
616度
10-27 645
MySQL GROUP BY用法
Group by用法
柯兄技术博客
05-29 3783
Group by 用途： 对结果集进行分组，常与汇总函数一起使用。 语法： SELECT column,SUM(column) FROM table GROUP BY column例： “Sales” 表中的原始数据 : Company                          Amount W3Schools                        
group by用法
站在地狱仰望天堂
06-10 1045
<br />group by在我之前的工作中不一定用到，可惜group by在一般的笔试测试的时候经常用到。所以今天刚开一个博客记录下group by用法，为避免以后再犯这个错误。 <br />  聚合函数 <br />求和函数——SUM() <br /><br />计数函数——COUNT() <br /><br />最大/最小值函数—MAX()/MIN() <br /><br />均值函数——AVG() <br /><br />———————————————————————————————————————
sql-group by用法
weixin_38775676的博客
03-23 710
group by:对数据进行分组 获取产品类别的单价合计 select CategoryName,ProductName,UnitPrice from northwind.categories right join northwind.products on northwind.categories.CategoryIDhahah = northwind.products.CategoryID
Sql group by 用法
热门推荐
风雨无阻学习之路
05-13 1万+
 Sql group by 用法：1、记住所谓分类汇总,就是说select后边不是分类字段就是汇总的字段.分类就是和group by后边的字段一致,汇总就是有聚集函数的字段2、select 后面的在聚合函數:Sum、Avg、Min、Max、Count中的列名不用出现在group by 后面中，其他的列都要 一个详细的例子：group by 分组通用数据库具有基于表的
sql语句group by用法
ma286388309的博客
03-01 9248
group by是sql中比较强大的功能，是在对数据分组统计时必不可少的用法。group_by的意思是根据by对数据按照一个或多个字段进行分组汇总。sql语句group by用法。
SQL Group by的使用
a15044774767的博客
12-05 657
OUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组，记下来通过本文给大家介绍SQL中Group By的用法，需要的朋友参考下吧GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。1、概述“Group By”从字面意义上理解就是根据“By”指定的规则对数据进行分组，所谓的分组就是将一个“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理。2、原
sql group by 用法
09-22
group by是一种在SQL中用来对数据进行分组的操作。它根据指定的列将数据集划分成若干个小区域，并对每个区域进行数据处理。通常与聚合函数一起使用，可以对每个区域进行汇总计算。在使用group by时，需要注意以下几...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值