文章目录
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 语法
1 2 3 4 5 |
SELECT <column_list> FROM < table name > WHERE <condition>GROUP BY <columns> [HAVING] <condition>; |
数据库结构
SQL GROUP BY with COUNT() function
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
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
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
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
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
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 |