SQL GROUP BY clause
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
Some important questions related to the SQL GROUP BY clause:
What is the purpose of the SQL GROUP BY clause?
Can you use multiple columns in the GROUP BY clause?
What is the difference between WHERE and HAVING clauses when used with GROUP BY?
Can you sort the result set using columns that are not included in the GROUP BY clause?
What happens if you use a column in the SELECT list that is not included in the GROUP BY clause?
Can you use aliases in the GROUP BY clause?
What is the order of execution for clauses in a SQL query with GROUP BY?
How do you include aggregated values in the result set along with grouped columns?
Can you use aggregate functions without the GROUP BY clause?
What are some common mistakes to avoid when using the GROUP BY clause?
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
employee_id first_name last_name email phone_number hire_date job_id salary commission_pct manager_id department_id ----------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- -------------- ---------- ------------- 100 Steven King SKING 515.123.4567 6/17/1987 AD_PRES 24000 90 101 Neena Kochhar NKOCHHAR 515.123.4568 6/18/1987 AD_VP 17000 100 90 102 Lex De Haan LDEHAAN 515.123.4569 6/19/1987 AD_VP 17000 100 90 103 Alexander Hunold AHUNOLD 590.423.4567 6/20/1987 IT_PROG 9000 102 60 104 Bruce Ernst BERNST 590.423.4568 6/21/1987 IT_PROG 6000 103 60 105 David Austin DAUSTIN 590.423.4569 6/22/1987 IT_PROG 4800 103 60 106 Valli Pataballa VPATABAL 590.423.4560 6/23/1987 IT_PROG 4800 103 60 107 Diana Lorentz DLORENTZ 590.423.5567 6/24/1987 IT_PROG 4200 103 60 108 Nancy Greenberg NGREENBE 515.124.4569 6/25/1987 FI_MGR 12000 101 100 109 Daniel Faviet DFAVIET 515.124.4169 6/26/1987 FI_ACCOUNT 9000 108 100 110 John Chen JCHEN 515.124.4269 6/27/1987 FI_ACCOUNT 8200 108 100 111 Ismael Sciarra ISCIARRA 515.124.4369 6/28/1987 FI_ACCOUNT 7700 108 100 112 Jose Manue Urman JMURMAN 515.124.4469 6/29/1987 FI_ACCOUNT 7800 108 100 113 Luis Popp LPOPP 515.124.4567 6/30/1987 FI_ACCOUNT 6900 108 100 114 Den Raphaely DRAPHEAL 515.127.4561 7/1/1987 PU_MAN 11000 100 30 115 Alexander Khoo AKHOO 515.127.4562 7/2/1987 PU_CLERK 3100 114 30 116 Shelli Baida SBAIDA 515.127.4563 7/3/1987 PU_CLERK 2900 114 30 117 Sigal Tobias STOBIAS 515.127.4564 7/4/1987 PU_CLERK 2800 114 30 118 Guy Himuro GHIMURO 515.127.4565 7/5/1987 PU_CLERK 2600 114 30 119 Karen Colmenares KCOLMENA 515.127.4566 7/6/1987 PU_CLERK 2500 114 30 120 Matthew Weiss MWEISS 650.123.1234 7/7/1987 ST_MAN 8000 100 50 121 Adam Fripp AFRIPP 650.123.2234 7/8/1987 ST_MAN 8200 100 50 122 Payam Kaufling PKAUFLIN 650.123.3234 7/9/1987 ST_MAN 7900 100 50 123 Shanta Vollman SVOLLMAN 650.123.4234 7/10/1987 ST_MAN 6500 100 50 124 Kevin Mourgos KMOURGOS 650.123.5234 7/11/1987 ST_MAN 5800 100 50 125 Julia Nayer JNAYER 650.124.1214 7/12/1987 ST_CLERK 3200 120 50 126 Irene Mikkilinen IMIKKILI 650.124.1224 7/13/1987 ST_CLERK 2700 120 50 127 James Landry JLANDRY 650.124.1334 7/14/1987 ST_CLERK 2400 120 50 128 Steven Markle SMARKLE 650.124.1434 7/15/1987 ST_CLERK 2200 120 50 129 Laura Bissot LBISSOT 650.124.5234 7/16/1987 ST_CLERK 3300 121 50 130 Mozhe Atkinson MATKINSO 650.124.6234 7/17/1987 ST_CLERK 2800 121 50 131 James Marlow JAMRLOW 650.124.7234 7/18/1987 ST_CLERK 2500 121 50 132 TJ Olson TJOLSON 650.124.8234 7/19/1987 ST_CLERK 2100 121 50 133 Jason Mallin JMALLIN 650.127.1934 7/20/1987 ST_CLERK 3300 122 50 134 Michael Rogers MROGERS 650.127.1834 7/21/1987 ST_CLERK 2900 122 50 135 Ki Gee KGEE 650.127.1734 7/22/1987 ST_CLERK 2400 122 50 136 Hazel Philtanker HPHILTAN 650.127.1634 7/23/1987 ST_CLERK 2200 122 50 137 Renske Ladwig RLADWIG 650.121.1234 7/24/1987 ST_CLERK 3600 123 50 138 Stephen Stiles SSTILES 650.121.2034 7/25/1987 ST_CLERK 3200 123 50 139 John Seo JSEO 650.121.2019 7/26/1987 ST_CLERK 2700 123 50 140 Joshua Patel JPATEL 650.121.1834 7/27/1987 ST_CLERK 2500 123 50 141 Trenna Rajs TRAJS 650.121.8009 7/28/1987 ST_CLERK 3500 124 50 142 Curtis Davies CDAVIES 650.121.2994 7/29/1987 ST_CLERK 3100 124 50 143 Randall Matos RMATOS 650.121.2874 7/30/1987 ST_CLERK 2600 124 50 144 Peter Vargas PVARGAS 650.121.2004 7/31/1987 ST_CLERK 2500 124 50 145 John Russell JRUSSEL 011.44.1344. 8/1/1987 SA_MAN 14000 0.4 100 80 146 Karen Partners KPARTNER 011.44.1344. 8/2/1987 SA_MAN 13500 0.3 100 80 147 Alberto Errazuriz AERRAZUR 011.44.1344. 8/3/1987 SA_MAN 12000 0.3 100 80 148 Gerald Cambrault GCAMBRAU 011.44.1344. 8/4/1987 SA_MAN 11000 0.3 100 80 149 Eleni Zlotkey EZLOTKEY 011.44.1344. 8/5/1987 SA_MAN 10500 0.2 100 80 150 Peter Tucker PTUCKER 011.44.1344. 8/6/1987 SA_REP 10000 0.3 145 80 151 David Bernstein DBERNSTE 011.44.1344. 8/7/1987 SA_REP 9500 0.25 145 80 152 Peter Hall PHALL 011.44.1344. 8/8/1987 SA_REP 9000 0.25 145 80 153 Christophe Olsen COLSEN 011.44.1344. 8/9/1987 SA_REP 8000 0.2 145 80 154 Nanette Cambrault NCAMBRAU 011.44.1344. 8/10/1987 SA_REP 7500 0.2 145 80 155 Oliver Tuvault OTUVAULT 011.44.1344. 8/11/1987 SA_REP 7000 0.15 145 80 156 Janette King JKING 011.44.1345. 8/12/1987 SA_REP 10000 0.35 146 80 157 Patrick Sully PSULLY 011.44.1345. 8/13/1987 SA_REP 9500 0.35 146 80 158 Allan McEwen AMCEWEN 011.44.1345. 8/14/1987 SA_REP 9000 0.35 146 80 159 Lindsey Smith LSMITH 011.44.1345. 8/15/1987 SA_REP 8000 0.3 146 80 160 Louise Doran LDORAN 011.44.1345. 8/16/1987 SA_REP 7500 0.3 146 80 161 Sarath Sewall SSEWALL 011.44.1345. 8/17/1987 SA_REP 7000 0.25 146 80 162 Clara Vishney CVISHNEY 011.44.1346. 8/18/1987 SA_REP 10500 0.25 147 80 163 Danielle Greene DGREENE 011.44.1346. 8/19/1987 SA_REP 9500 0.15 147 80 164 Mattea Marvins MMARVINS 011.44.1346. 8/20/1987 SA_REP 7200 0.1 147 80 165 David Lee DLEE 011.44.1346. 8/21/1987 SA_REP 6800 0.1 147 80 166 Sundar Ande SANDE 011.44.1346. 8/22/1987 SA_REP 6400 0.1 147 80 167 Amit Banda ABANDA 011.44.1346. 8/23/1987 SA_REP 6200 0.1 147 80 168 Lisa Ozer LOZER 011.44.1343. 8/24/1987 SA_REP 11500 0.25 148 80 169 Harrison Bloom HBLOOM 011.44.1343. 8/25/1987 SA_REP 10000 0.2 148 80 170 Tayler Fox TFOX 011.44.1343. 8/26/1987 SA_REP 9600 0.2 148 80 171 William Smith WSMITH 011.44.1343. 8/27/1987 SA_REP 7400 0.15 148 80 172 Elizabeth Bates EBATES 011.44.1343. 8/28/1987 SA_REP 7300 0.15 148 80 173 Sundita Kumar SKUMAR 011.44.1343. 8/29/1987 SA_REP 6100 0.1 148 80 174 Ellen Abel EABEL 011.44.1644. 8/30/1987 SA_REP 11000 0.3 149 80 175 Alyssa Hutton AHUTTON 011.44.1644. 8/31/1987 SA_REP 8800 0.25 149 80 176 Jonathon Taylor JTAYLOR 011.44.1644. 9/1/1987 SA_REP 8600 0.2 149 80 177 Jack Livingston JLIVINGS 011.44.1644. 9/2/1987 SA_REP 8400 0.2 149 80 178 Kimberely Grant KGRANT 011.44.1644. 9/3/1987 SA_REP 7000 0.15 149 179 Charles Johnson CJOHNSON 011.44.1644. 9/4/1987 SA_REP 6200 0.1 149 80 180 Winston Taylor WTAYLOR 650.507.9876 9/5/1987 SH_CLERK 3200 120 50 181 Jean Fleaur JFLEAUR 650.507.9877 9/6/1987 SH_CLERK 3100 120 50 182 Martha Sullivan MSULLIVA 650.507.9878 9/7/1987 SH_CLERK 2500 120 50 183 Girard Geoni GGEONI 650.507.9879 9/8/1987 SH_CLERK 2800 120 50 184 Nandita Sarchand NSARCHAN 650.509.1876 9/9/1987 SH_CLERK 4200 121 50 185 Alexis Bull ABULL 650.509.2876 9/10/1987 SH_CLERK 4100 121 50 186 Julia Dellinger JDELLING 650.509.3876 9/11/1987 SH_CLERK 3400 121 50 187 Anthony Cabrio ACABRIO 650.509.4876 9/12/1987 SH_CLERK 3000 121 50 188 Kelly Chung KCHUNG 650.505.1876 9/13/1987 SH_CLERK 3800 122 50 189 Jennifer Dilly JDILLY 650.505.2876 9/14/1987 SH_CLERK 3600 122 50 190 Timothy Gates TGATES 650.505.3876 9/15/1987 SH_CLERK 2900 122 50 191 Randall Perkins RPERKINS 650.505.4876 9/16/1987 SH_CLERK 2500 122 50 192 Sarah Bell SBELL 650.501.1876 9/17/1987 SH_CLERK 4000 123 50 193 Britney Everett BEVERETT 650.501.2876 9/18/1987 SH_CLERK 3900 123 50 194 Samuel McCain SMCCAIN 650.501.3876 9/19/1987 SH_CLERK 3200 123 50 195 Vance Jones VJONES 650.501.4876 9/20/1987 SH_CLERK 2800 123 50 196 Alana Walsh AWALSH 650.507.9811 9/21/1987 SH_CLERK 3100 124 50 197 Kevin Feeney KFEENEY 650.507.9822 9/22/1987 SH_CLERK 3000 124 50 198 Donald OConnell DOCONNEL 650.507.9833 9/23/1987 SH_CLERK 2600 124 50 199 Douglas Grant DGRANT 650.507.9844 9/24/1987 SH_CLERK 2600 124 50 200 Jennifer Whalen JWHALEN 515.123.4444 9/25/1987 AD_ASST 4400 101 10 201 Michael Hartstein MHARTSTE 515.123.5555 9/26/1987 MK_MAN 13000 100 20 202 Pat Fay PFAY 603.123.6666 9/27/1987 MK_REP 6000 201 20 203 Susan Mavris SMAVRIS 515.123.7777 9/28/1987 HR_REP 6500 101 40 204 Hermann Baer HBAER 515.123.8888 9/29/1987 PR_REP 10000 101 70 205 Shelley Higgins SHIGGINS 515.123.8080 9/30/1987 AC_MGR 12000 101 110 206 William Gietz WGIETZ 515.123.8181 10/1/1987 AC_ACCOUNT 8300 205 110
SQL Code:
-- This SQL query calculates the number of employees in each department and displays the department code along with the count.
-- SELECT statement begins
SELECT
department_id "Department Code", -- Selects the 'department_id' column and renames it as 'Department Code' in the result set
COUNT(*) "No of Employees" -- Counts the number of rows for each department and renames the result column as 'No of Employees'
FROM
employees -- Specifies the 'employees' table to retrieve data from
GROUP BY
department_id; -- Groups the result set by the 'department_id' column
Explanation:
- This SQL code is a SELECT statement that retrieves data from the 'employees' table.
- It calculates the number of employees in each department and displays the department code along with the count.
- The SELECT clause selects the 'department_id' column from the 'employees' table and renames it as 'Department Code' in the result set.
- The COUNT(*) function counts the number of rows for each department.
- The result column from the COUNT(*) function is renamed as 'No of Employees'.
- The GROUP BY clause groups the result set by the 'department_id' column, ensuring that the count is calculated for each unique department.
- The query does not filter or manipulate the data further; it simply retrieves the department code and the count of employees for each department from the 'employees' table.
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
employee_id first_name last_name email phone_number hire_date job_id salary commission_pct manager_id department_id ----------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- -------------- ---------- ------------- 100 Steven King SKING 515.123.4567 6/17/1987 AD_PRES 24000 90 101 Neena Kochhar NKOCHHAR 515.123.4568 6/18/1987 AD_VP 17000 100 90 102 Lex De Haan LDEHAAN 515.123.4569 6/19/1987 AD_VP 17000 100 90 103 Alexander Hunold AHUNOLD 590.423.4567 6/20/1987 IT_PROG 9000 102 60 104 Bruce Ernst BERNST 590.423.4568 6/21/1987 IT_PROG 6000 103 60 105 David Austin DAUSTIN 590.423.4569 6/22/1987 IT_PROG 4800 103 60 106 Valli Pataballa VPATABAL 590.423.4560 6/23/1987 IT_PROG 4800 103 60 107 Diana Lorentz DLORENTZ 590.423.5567 6/24/1987 IT_PROG 4200 103 60 108 Nancy Greenberg NGREENBE 515.124.4569 6/25/1987 FI_MGR 12000 101 100 109 Daniel Faviet DFAVIET 515.124.4169 6/26/1987 FI_ACCOUNT 9000 108 100 110 John Chen JCHEN 515.124.4269 6/27/1987 FI_ACCOUNT 8200 108 100 111 Ismael Sciarra ISCIARRA 515.124.4369 6/28/1987 FI_ACCOUNT 7700 108 100 112 Jose Manue Urman JMURMAN 515.124.4469 6/29/1987 FI_ACCOUNT 7800 108 100 113 Luis Popp LPOPP 515.124.4567 6/30/1987 FI_ACCOUNT 6900 108 100 114 Den Raphaely DRAPHEAL 515.127.4561 7/1/1987 PU_MAN 11000 100 30 115 Alexander Khoo AKHOO 515.127.4562 7/2/1987 PU_CLERK 3100 114 30 116 Shelli Baida SBAIDA 515.127.4563 7/3/1987 PU_CLERK 2900 114 30 117 Sigal Tobias STOBIAS 515.127.4564 7/4/1987 PU_CLERK 2800 114 30 118 Guy Himuro GHIMURO 515.127.4565 7/5/1987 PU_CLERK 2600 114 30 119 Karen Colmenares KCOLMENA 515.127.4566 7/6/1987 PU_CLERK 2500 114 30 120 Matthew Weiss MWEISS 650.123.1234 7/7/1987 ST_MAN 8000 100 50 121 Adam Fripp AFRIPP 650.123.2234 7/8/1987 ST_MAN 8200 100 50 122 Payam Kaufling PKAUFLIN 650.123.3234 7/9/1987 ST_MAN 7900 100 50 123 Shanta Vollman SVOLLMAN 650.123.4234 7/10/1987 ST_MAN 6500 100 50 124 Kevin Mourgos KMOURGOS 650.123.5234 7/11/1987 ST_MAN 5800 100 50 125 Julia Nayer JNAYER 650.124.1214 7/12/1987 ST_CLERK 3200 120 50 126 Irene Mikkilinen IMIKKILI 650.124.1224 7/13/1987 ST_CLERK 2700 120 50 127 James Landry JLANDRY 650.124.1334 7/14/1987 ST_CLERK 2400 120 50 128 Steven Markle SMARKLE 650.124.1434 7/15/1987 ST_CLERK 2200 120 50 129 Laura Bissot LBISSOT 650.124.5234 7/16/1987 ST_CLERK 3300 121 50 130 Mozhe Atkinson MATKINSO 650.124.6234 7/17/1987 ST_CLERK 2800 121 50 131 James Marlow JAMRLOW 650.124.7234 7/18/1987 ST_CLERK 2500 121 50 132 TJ Olson TJOLSON 650.124.8234 7/19/1987 ST_CLERK 2100 121 50 133 Jason Mallin JMALLIN 650.127.1934 7/20/1987 ST_CLERK 3300 122 50 134 Michael Rogers MROGERS 650.127.1834 7/21/1987 ST_CLERK 2900 122 50 135 Ki Gee KGEE 650.127.1734 7/22/1987 ST_CLERK 2400 122 50 136 Hazel Philtanker HPHILTAN 650.127.1634 7/23/1987 ST_CLERK 2200 122 50 137 Renske Ladwig RLADWIG 650.121.1234 7/24/1987 ST_CLERK 3600 123 50 138 Stephen Stiles SSTILES 650.121.2034 7/25/1987 ST_CLERK 3200 123 50 139 John Seo JSEO 650.121.2019 7/26/1987 ST_CLERK 2700 123 50 140 Joshua Patel JPATEL 650.121.1834 7/27/1987 ST_CLERK 2500 123 50 141 Trenna Rajs TRAJS 650.121.8009 7/28/1987 ST_CLERK 3500 124 50 142 Curtis Davies CDAVIES 650.121.2994 7/29/1987 ST_CLERK 3100 124 50 143 Randall Matos RMATOS 650.121.2874 7/30/1987 ST_CLERK 2600 124 50 144 Peter Vargas PVARGAS 650.121.2004 7/31/1987 ST_CLERK 2500 124 50 145 John Russell JRUSSEL 011.44.1344. 8/1/1987 SA_MAN 14000 0.4 100 80 146 Karen Partners KPARTNER 011.44.1344. 8/2/1987 SA_MAN 13500 0.3 100 80 147 Alberto Errazuriz AERRAZUR 011.44.1344. 8/3/1987 SA_MAN 12000 0.3 100 80 148 Gerald Cambrault GCAMBRAU 011.44.1344. 8/4/1987 SA_MAN 11000 0.3 100 80 149 Eleni Zlotkey EZLOTKEY 011.44.1344. 8/5/1987 SA_MAN 10500 0.2 100 80 150 Peter Tucker PTUCKER 011.44.1344. 8/6/1987 SA_REP 10000 0.3 145 80 151 David Bernstein DBERNSTE 011.44.1344. 8/7/1987 SA_REP 9500 0.25 145 80 152 Peter Hall PHALL 011.44.1344. 8/8/1987 SA_REP 9000 0.25 145 80 153 Christophe Olsen COLSEN 011.44.1344. 8/9/1987 SA_REP 8000 0.2 145 80 154 Nanette Cambrault NCAMBRAU 011.44.1344. 8/10/1987 SA_REP 7500 0.2 145 80 155 Oliver Tuvault OTUVAULT 011.44.1344. 8/11/1987 SA_REP 7000 0.15 145 80 156 Janette King JKING 011.44.1345. 8/12/1987 SA_REP 10000 0.35 146 80 157 Patrick Sully PSULLY 011.44.1345. 8/13/1987 SA_REP 9500 0.35 146 80 158 Allan McEwen AMCEWEN 011.44.1345. 8/14/1987 SA_REP 9000 0.35 146 80 159 Lindsey Smith LSMITH 011.44.1345. 8/15/1987 SA_REP 8000 0.3 146 80 160 Louise Doran LDORAN 011.44.1345. 8/16/1987 SA_REP 7500 0.3 146 80 161 Sarath Sewall SSEWALL 011.44.1345. 8/17/1987 SA_REP 7000 0.25 146 80 162 Clara Vishney CVISHNEY 011.44.1346. 8/18/1987 SA_REP 10500 0.25 147 80 163 Danielle Greene DGREENE 011.44.1346. 8/19/1987 SA_REP 9500 0.15 147 80 164 Mattea Marvins MMARVINS 011.44.1346. 8/20/1987 SA_REP 7200 0.1 147 80 165 David Lee DLEE 011.44.1346. 8/21/1987 SA_REP 6800 0.1 147 80 166 Sundar Ande SANDE 011.44.1346. 8/22/1987 SA_REP 6400 0.1 147 80 167 Amit Banda ABANDA 011.44.1346. 8/23/1987 SA_REP 6200 0.1 147 80 168 Lisa Ozer LOZER 011.44.1343. 8/24/1987 SA_REP 11500 0.25 148 80 169 Harrison Bloom HBLOOM 011.44.1343. 8/25/1987 SA_REP 10000 0.2 148 80 170 Tayler Fox TFOX 011.44.1343. 8/26/1987 SA_REP 9600 0.2 148 80 171 William Smith WSMITH 011.44.1343. 8/27/1987 SA_REP 7400 0.15 148 80 172 Elizabeth Bates EBATES 011.44.1343. 8/28/1987 SA_REP 7300 0.15 148 80 173 Sundita Kumar SKUMAR 011.44.1343. 8/29/1987 SA_REP 6100 0.1 148 80 174 Ellen Abel EABEL 011.44.1644. 8/30/1987 SA_REP 11000 0.3 149 80 175 Alyssa Hutton AHUTTON 011.44.1644. 8/31/1987 SA_REP 8800 0.25 149 80 176 Jonathon Taylor JTAYLOR 011.44.1644. 9/1/1987 SA_REP 8600 0.2 149 80 177 Jack Livingston JLIVINGS 011.44.1644. 9/2/1987 SA_REP 8400 0.2 149 80 178 Kimberely Grant KGRANT 011.44.1644. 9/3/1987 SA_REP 7000 0.15 149 179 Charles Johnson CJOHNSON 011.44.1644. 9/4/1987 SA_REP 6200 0.1 149 80 180 Winston Taylor WTAYLOR 650.507.9876 9/5/1987 SH_CLERK 3200 120 50 181 Jean Fleaur JFLEAUR 650.507.9877 9/6/1987 SH_CLERK 3100 120 50 182 Martha Sullivan MSULLIVA 650.507.9878 9/7/1987 SH_CLERK 2500 120 50 183 Girard Geoni GGEONI 650.507.9879 9/8/1987 SH_CLERK 2800 120 50 184 Nandita Sarchand NSARCHAN 650.509.1876 9/9/1987 SH_CLERK 4200 121 50 185 Alexis Bull ABULL 650.509.2876 9/10/1987 SH_CLERK 4100 121 50 186 Julia Dellinger JDELLING 650.509.3876 9/11/1987 SH_CLERK 3400 121 50 187 Anthony Cabrio ACABRIO 650.509.4876 9/12/1987 SH_CLERK 3000 121 50 188 Kelly Chung KCHUNG 650.505.1876 9/13/1987 SH_CLERK 3800 122 50 189 Jennifer Dilly JDILLY 650.505.2876 9/14/1987 SH_CLERK 3600 122 50 190 Timothy Gates TGATES 650.505.3876 9/15/1987 SH_CLERK 2900 122 50 191 Randall Perkins RPERKINS 650.505.4876 9/16/1987 SH_CLERK 2500 122 50 192 Sarah Bell SBELL 650.501.1876 9/17/1987 SH_CLERK 4000 123 50 193 Britney Everett BEVERETT 650.501.2876 9/18/1987 SH_CLERK 3900 123 50 194 Samuel McCain SMCCAIN 650.501.3876 9/19/1987 SH_CLERK 3200 123 50 195 Vance Jones VJONES 650.501.4876 9/20/1987 SH_CLERK 2800 123 50 196 Alana Walsh AWALSH 650.507.9811 9/21/1987 SH_CLERK 3100 124 50 197 Kevin Feeney KFEENEY 650.507.9822 9/22/1987 SH_CLERK 3000 124 50 198 Donald OConnell DOCONNEL 650.507.9833 9/23/1987 SH_CLERK 2600 124 50 199 Douglas Grant DGRANT 650.507.9844 9/24/1987 SH_CLERK 2600 124 50 200 Jennifer Whalen JWHALEN 515.123.4444 9/25/1987 AD_ASST 4400 101 10 201 Michael Hartstein MHARTSTE 515.123.5555 9/26/1987 MK_MAN 13000 100 20 202 Pat Fay PFAY 603.123.6666 9/27/1987 MK_REP 6000 201 20 203 Susan Mavris SMAVRIS 515.123.7777 9/28/1987 HR_REP 6500 101 40 204 Hermann Baer HBAER 515.123.8888 9/29/1987 PR_REP 10000 101 70 205 Shelley Higgins SHIGGINS 515.123.8080 9/30/1987 AC_MGR 12000 101 110 206 William Gietz WGIETZ 515.123.8181 10/1/1987 AC_ACCOUNT 8300 205 110
SQL Code:
-- This SQL query calculates the total salary for each department and displays the department ID along with the sum of salaries.
-- SELECT statement begins
SELECT
department_id, -- Selects the 'department_id' column
SUM(salary) -- Calculates the sum of salaries for each department
FROM
employees -- Specifies the 'employees' table to retrieve data from
GROUP BY
department_id; -- Groups the result set by the 'department_id' column
Explanation:
- This SQL code is a SELECT statement that retrieves data from the 'employees' table.
- It calculates the total salary for each department and displays the department ID along with the sum of salaries.
- The SELECT clause selects the 'department_id' column from the 'employees' table.
- The SUM(salary) function calculates the sum of salaries for each department.
- The GROUP BY clause groups the result set by the 'department_id' column, ensuring that the sum is calculated for each unique department.
- The query does not filter or manipulate the data further; it simply retrieves the department ID and the sum of salaries for each department from the 'employees' table.
Relational Algebra Expression:
Relational Algebra Tree:
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
employee_id first_name last_name email phone_number hire_date job_id salary commission_pct manager_id department_id ----------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- -------------- ---------- ------------- 100 Steven King SKING 515.123.4567 6/17/1987 AD_PRES 24000 90 101 Neena Kochhar NKOCHHAR 515.123.4568 6/18/1987 AD_VP 17000 100 90 102 Lex De Haan LDEHAAN 515.123.4569 6/19/1987 AD_VP 17000 100 90 103 Alexander Hunold AHUNOLD 590.423.4567 6/20/1987 IT_PROG 9000 102 60 104 Bruce Ernst BERNST 590.423.4568 6/21/1987 IT_PROG 6000 103 60 105 David Austin DAUSTIN 590.423.4569 6/22/1987 IT_PROG 4800 103 60 106 Valli Pataballa VPATABAL 590.423.4560 6/23/1987 IT_PROG 4800 103 60 107 Diana Lorentz DLORENTZ 590.423.5567 6/24/1987 IT_PROG 4200 103 60 108 Nancy Greenberg NGREENBE 515.124.4569 6/25/1987 FI_MGR 12000 101 100 109 Daniel Faviet DFAVIET 515.124.4169 6/26/1987 FI_ACCOUNT 9000 108 100 110 John Chen JCHEN 515.124.4269 6/27/1987 FI_ACCOUNT 8200 108 100 111 Ismael Sciarra ISCIARRA 515.124.4369 6/28/1987 FI_ACCOUNT 7700 108 100 112 Jose Manue Urman JMURMAN 515.124.4469 6/29/1987 FI_ACCOUNT 7800 108 100 113 Luis Popp LPOPP 515.124.4567 6/30/1987 FI_ACCOUNT 6900 108 100 114 Den Raphaely DRAPHEAL 515.127.4561 7/1/1987 PU_MAN 11000 100 30 115 Alexander Khoo AKHOO 515.127.4562 7/2/1987 PU_CLERK 3100 114 30 116 Shelli Baida SBAIDA 515.127.4563 7/3/1987 PU_CLERK 2900 114 30 117 Sigal Tobias STOBIAS 515.127.4564 7/4/1987 PU_CLERK 2800 114 30 118 Guy Himuro GHIMURO 515.127.4565 7/5/1987 PU_CLERK 2600 114 30 119 Karen Colmenares KCOLMENA 515.127.4566 7/6/1987 PU_CLERK 2500 114 30 120 Matthew Weiss MWEISS 650.123.1234 7/7/1987 ST_MAN 8000 100 50 121 Adam Fripp AFRIPP 650.123.2234 7/8/1987 ST_MAN 8200 100 50 122 Payam Kaufling PKAUFLIN 650.123.3234 7/9/1987 ST_MAN 7900 100 50 123 Shanta Vollman SVOLLMAN 650.123.4234 7/10/1987 ST_MAN 6500 100 50 124 Kevin Mourgos KMOURGOS 650.123.5234 7/11/1987 ST_MAN 5800 100 50 125 Julia Nayer JNAYER 650.124.1214 7/12/1987 ST_CLERK 3200 120 50 126 Irene Mikkilinen IMIKKILI 650.124.1224 7/13/1987 ST_CLERK 2700 120 50 127 James Landry JLANDRY 650.124.1334 7/14/1987 ST_CLERK 2400 120 50 128 Steven Markle SMARKLE 650.124.1434 7/15/1987 ST_CLERK 2200 120 50 129 Laura Bissot LBISSOT 650.124.5234 7/16/1987 ST_CLERK 3300 121 50 130 Mozhe Atkinson MATKINSO 650.124.6234 7/17/1987 ST_CLERK 2800 121 50 131 James Marlow JAMRLOW 650.124.7234 7/18/1987 ST_CLERK 2500 121 50 132 TJ Olson TJOLSON 650.124.8234 7/19/1987 ST_CLERK 2100 121 50 133 Jason Mallin JMALLIN 650.127.1934 7/20/1987 ST_CLERK 3300 122 50 134 Michael Rogers MROGERS 650.127.1834 7/21/1987 ST_CLERK 2900 122 50 135 Ki Gee KGEE 650.127.1734 7/22/1987 ST_CLERK 2400 122 50 136 Hazel Philtanker HPHILTAN 650.127.1634 7/23/1987 ST_CLERK 2200 122 50 137 Renske Ladwig RLADWIG 650.121.1234 7/24/1987 ST_CLERK 3600 123 50 138 Stephen Stiles SSTILES 650.121.2034 7/25/1987 ST_CLERK 3200 123 50 139 John Seo JSEO 650.121.2019 7/26/1987 ST_CLERK 2700 123 50 140 Joshua Patel JPATEL 650.121.1834 7/27/1987 ST_CLERK 2500 123 50 141 Trenna Rajs TRAJS 650.121.8009 7/28/1987 ST_CLERK 3500 124 50 142 Curtis Davies CDAVIES 650.121.2994 7/29/1987 ST_CLERK 3100 124 50 143 Randall Matos RMATOS 650.121.2874 7/30/1987 ST_CLERK 2600 124 50 144 Peter Vargas PVARGAS 650.121.2004 7/31/1987 ST_CLERK 2500 124 50 145 John Russell JRUSSEL 011.44.1344. 8/1/1987 SA_MAN 14000 0.4 100 80 146 Karen Partners KPARTNER 011.44.1344. 8/2/1987 SA_MAN 13500 0.3 100 80 147 Alberto Errazuriz AERRAZUR 011.44.1344. 8/3/1987 SA_MAN 12000 0.3 100 80 148 Gerald Cambrault GCAMBRAU 011.44.1344. 8/4/1987 SA_MAN 11000 0.3 100 80 149 Eleni Zlotkey EZLOTKEY 011.44.1344. 8/5/1987 SA_MAN 10500 0.2 100 80 150 Peter Tucker PTUCKER 011.44.1344. 8/6/1987 SA_REP 10000 0.3 145 80 151 David Bernstein DBERNSTE 011.44.1344. 8/7/1987 SA_REP 9500 0.25 145 80 152 Peter Hall PHALL 011.44.1344. 8/8/1987 SA_REP 9000 0.25 145 80 153 Christophe Olsen COLSEN 011.44.1344. 8/9/1987 SA_REP 8000 0.2 145 80 154 Nanette Cambrault NCAMBRAU 011.44.1344. 8/10/1987 SA_REP 7500 0.2 145 80 155 Oliver Tuvault OTUVAULT 011.44.1344. 8/11/1987 SA_REP 7000 0.15 145 80 156 Janette King JKING 011.44.1345. 8/12/1987 SA_REP 10000 0.35 146 80 157 Patrick Sully PSULLY 011.44.1345. 8/13/1987 SA_REP 9500 0.35 146 80 158 Allan McEwen AMCEWEN 011.44.1345. 8/14/1987 SA_REP 9000 0.35 146 80 159 Lindsey Smith LSMITH 011.44.1345. 8/15/1987 SA_REP 8000 0.3 146 80 160 Louise Doran LDORAN 011.44.1345. 8/16/1987 SA_REP 7500 0.3 146 80 161 Sarath Sewall SSEWALL 011.44.1345. 8/17/1987 SA_REP 7000 0.25 146 80 162 Clara Vishney CVISHNEY 011.44.1346. 8/18/1987 SA_REP 10500 0.25 147 80 163 Danielle Greene DGREENE 011.44.1346. 8/19/1987 SA_REP 9500 0.15 147 80 164 Mattea Marvins MMARVINS 011.44.1346. 8/20/1987 SA_REP 7200 0.1 147 80 165 David Lee DLEE 011.44.1346. 8/21/1987 SA_REP 6800 0.1 147 80 166 Sundar Ande SANDE 011.44.1346. 8/22/1987 SA_REP 6400 0.1 147 80 167 Amit Banda ABANDA 011.44.1346. 8/23/1987 SA_REP 6200 0.1 147 80 168 Lisa Ozer LOZER 011.44.1343. 8/24/1987 SA_REP 11500 0.25 148 80 169 Harrison Bloom HBLOOM 011.44.1343. 8/25/1987 SA_REP 10000 0.2 148 80 170 Tayler Fox TFOX 011.44.1343. 8/26/1987 SA_REP 9600 0.2 148 80 171 William Smith WSMITH 011.44.1343. 8/27/1987 SA_REP 7400 0.15 148 80 172 Elizabeth Bates EBATES 011.44.1343. 8/28/1987 SA_REP 7300 0.15 148 80 173 Sundita Kumar SKUMAR 011.44.1343. 8/29/1987 SA_REP 6100 0.1 148 80 174 Ellen Abel EABEL 011.44.1644. 8/30/1987 SA_REP 11000 0.3 149 80 175 Alyssa Hutton AHUTTON 011.44.1644. 8/31/1987 SA_REP 8800 0.25 149 80 176 Jonathon Taylor JTAYLOR 011.44.1644. 9/1/1987 SA_REP 8600 0.2 149 80 177 Jack Livingston JLIVINGS 011.44.1644. 9/2/1987 SA_REP 8400 0.2 149 80 178 Kimberely Grant KGRANT 011.44.1644. 9/3/1987 SA_REP 7000 0.15 149 179 Charles Johnson CJOHNSON 011.44.1644. 9/4/1987 SA_REP 6200 0.1 149 80 180 Winston Taylor WTAYLOR 650.507.9876 9/5/1987 SH_CLERK 3200 120 50 181 Jean Fleaur JFLEAUR 650.507.9877 9/6/1987 SH_CLERK 3100 120 50 182 Martha Sullivan MSULLIVA 650.507.9878 9/7/1987 SH_CLERK 2500 120 50 183 Girard Geoni GGEONI 650.507.9879 9/8/1987 SH_CLERK 2800 120 50 184 Nandita Sarchand NSARCHAN 650.509.1876 9/9/1987 SH_CLERK 4200 121 50 185 Alexis Bull ABULL 650.509.2876 9/10/1987 SH_CLERK 4100 121 50 186 Julia Dellinger JDELLING 650.509.3876 9/11/1987 SH_CLERK 3400 121 50 187 Anthony Cabrio ACABRIO 650.509.4876 9/12/1987 SH_CLERK 3000 121 50 188 Kelly Chung KCHUNG 650.505.1876 9/13/1987 SH_CLERK 3800 122 50 189 Jennifer Dilly JDILLY 650.505.2876 9/14/1987 SH_CLERK 3600 122 50 190 Timothy Gates TGATES 650.505.3876 9/15/1987 SH_CLERK 2900 122 50 191 Randall Perkins RPERKINS 650.505.4876 9/16/1987 SH_CLERK 2500 122 50 192 Sarah Bell SBELL 650.501.1876 9/17/1987 SH_CLERK 4000 123 50 193 Britney Everett BEVERETT 650.501.2876 9/18/1987 SH_CLERK 3900 123 50 194 Samuel McCain SMCCAIN 650.501.3876 9/19/1987 SH_CLERK 3200 123 50 195 Vance Jones VJONES 650.501.4876 9/20/1987 SH_CLERK 2800 123 50 196 Alana Walsh AWALSH 650.507.9811 9/21/1987 SH_CLERK 3100 124 50 197 Kevin Feeney KFEENEY 650.507.9822 9/22/1987 SH_CLERK 3000 124 50 198 Donald OConnell DOCONNEL 650.507.9833 9/23/1987 SH_CLERK 2600 124 50 199 Douglas Grant DGRANT 650.507.9844 9/24/1987 SH_CLERK 2600 124 50 200 Jennifer Whalen JWHALEN 515.123.4444 9/25/1987 AD_ASST 4400 101 10 201 Michael Hartstein MHARTSTE 515.123.5555 9/26/1987 MK_MAN 13000 100 20 202 Pat Fay PFAY 603.123.6666 9/27/1987 MK_REP 6000 201 20 203 Susan Mavris SMAVRIS 515.123.7777 9/28/1987 HR_REP 6500 101 40 204 Hermann Baer HBAER 515.123.8888 9/29/1987 PR_REP 10000 101 70 205 Shelley Higgins SHIGGINS 515.123.8080 9/30/1987 AC_MGR 12000 101 110 206 William Gietz WGIETZ 515.123.8181 10/1/1987 AC_ACCOUNT 8300 205 110
SQL Code:
-- This SQL query calculates the number of employees, total salary, and displays the department code for each department.
-- SELECT statement begins
SELECT
department_id "Department Code", -- Selects the 'department_id' column and renames it as 'Department Code' in the result set
COUNT(*) "No of Employees", -- Counts the number of rows for each department and renames the result column as 'No of Employees'
SUM(salary) "Total Salary" -- Calculates the sum of salaries for each department and renames the result column as 'Total Salary'
FROM
employees -- Specifies the 'employees' table to retrieve data from
GROUP BY
department_id; -- Groups the result set by the 'department_id' column
Explanation:
- This SQL code is a SELECT statement that retrieves data from the 'employees' table.
- It calculates the number of employees, total salary, and displays the department code for each department.
- The SELECT clause selects the 'department_id' column from the 'employees' table and renames it as 'Department Code' in the result set.
- The COUNT(*) function counts the number of rows for each department and renames the result column as 'No of Employees'.
- The SUM(salary) function calculates the sum of salaries for each department and renames the result column as 'Total Salary'.
- The GROUP BY clause groups the result set by the 'department_id' column, ensuring that calculations are performed for each unique department.
- The query does not filter or manipulate the data further; it simply retrieves the department code, the number of employees, and the total salary for each department from the 'employees' table.
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
The following query displays the department code, job id, total salary paid to employees group by department_id, job_id.
Sample table: employees
employee_id first_name last_name email phone_number hire_date job_id salary commission_pct manager_id department_id ----------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- -------------- ---------- ------------- 100 Steven King SKING 515.123.4567 6/17/1987 AD_PRES 24000 90 101 Neena Kochhar NKOCHHAR 515.123.4568 6/18/1987 AD_VP 17000 100 90 102 Lex De Haan LDEHAAN 515.123.4569 6/19/1987 AD_VP 17000 100 90 103 Alexander Hunold AHUNOLD 590.423.4567 6/20/1987 IT_PROG 9000 102 60 104 Bruce Ernst BERNST 590.423.4568 6/21/1987 IT_PROG 6000 103 60 105 David Austin DAUSTIN 590.423.4569 6/22/1987 IT_PROG 4800 103 60 106 Valli Pataballa VPATABAL 590.423.4560 6/23/1987 IT_PROG 4800 103 60 107 Diana Lorentz DLORENTZ 590.423.5567 6/24/1987 IT_PROG 4200 103 60 108 Nancy Greenberg NGREENBE 515.124.4569 6/25/1987 FI_MGR 12000 101 100 109 Daniel Faviet DFAVIET 515.124.4169 6/26/1987 FI_ACCOUNT 9000 108 100 110 John Chen JCHEN 515.124.4269 6/27/1987 FI_ACCOUNT 8200 108 100 111 Ismael Sciarra ISCIARRA 515.124.4369 6/28/1987 FI_ACCOUNT 7700 108 100 112 Jose Manue Urman JMURMAN 515.124.4469 6/29/1987 FI_ACCOUNT 7800 108 100 113 Luis Popp LPOPP 515.124.4567 6/30/1987 FI_ACCOUNT 6900 108 100 114 Den Raphaely DRAPHEAL 515.127.4561 7/1/1987 PU_MAN 11000 100 30 115 Alexander Khoo AKHOO 515.127.4562 7/2/1987 PU_CLERK 3100 114 30 116 Shelli Baida SBAIDA 515.127.4563 7/3/1987 PU_CLERK 2900 114 30 117 Sigal Tobias STOBIAS 515.127.4564 7/4/1987 PU_CLERK 2800 114 30 118 Guy Himuro GHIMURO 515.127.4565 7/5/1987 PU_CLERK 2600 114 30 119 Karen Colmenares KCOLMENA 515.127.4566 7/6/1987 PU_CLERK 2500 114 30 120 Matthew Weiss MWEISS 650.123.1234 7/7/1987 ST_MAN 8000 100 50 121 Adam Fripp AFRIPP 650.123.2234 7/8/1987 ST_MAN 8200 100 50 122 Payam Kaufling PKAUFLIN 650.123.3234 7/9/1987 ST_MAN 7900 100 50 123 Shanta Vollman SVOLLMAN 650.123.4234 7/10/1987 ST_MAN 6500 100 50 124 Kevin Mourgos KMOURGOS 650.123.5234 7/11/1987 ST_MAN 5800 100 50 125 Julia Nayer JNAYER 650.124.1214 7/12/1987 ST_CLERK 3200 120 50 126 Irene Mikkilinen IMIKKILI 650.124.1224 7/13/1987 ST_CLERK 2700 120 50 127 James Landry JLANDRY 650.124.1334 7/14/1987 ST_CLERK 2400 120 50 128 Steven Markle SMARKLE 650.124.1434 7/15/1987 ST_CLERK 2200 120 50 129 Laura Bissot LBISSOT 650.124.5234 7/16/1987 ST_CLERK 3300 121 50 130 Mozhe Atkinson MATKINSO 650.124.6234 7/17/1987 ST_CLERK 2800 121 50 131 James Marlow JAMRLOW 650.124.7234 7/18/1987 ST_CLERK 2500 121 50 132 TJ Olson TJOLSON 650.124.8234 7/19/1987 ST_CLERK 2100 121 50 133 Jason Mallin JMALLIN 650.127.1934 7/20/1987 ST_CLERK 3300 122 50 134 Michael Rogers MROGERS 650.127.1834 7/21/1987 ST_CLERK 2900 122 50 135 Ki Gee KGEE 650.127.1734 7/22/1987 ST_CLERK 2400 122 50 136 Hazel Philtanker HPHILTAN 650.127.1634 7/23/1987 ST_CLERK 2200 122 50 137 Renske Ladwig RLADWIG 650.121.1234 7/24/1987 ST_CLERK 3600 123 50 138 Stephen Stiles SSTILES 650.121.2034 7/25/1987 ST_CLERK 3200 123 50 139 John Seo JSEO 650.121.2019 7/26/1987 ST_CLERK 2700 123 50 140 Joshua Patel JPATEL 650.121.1834 7/27/1987 ST_CLERK 2500 123 50 141 Trenna Rajs TRAJS 650.121.8009 7/28/1987 ST_CLERK 3500 124 50 142 Curtis Davies CDAVIES 650.121.2994 7/29/1987 ST_CLERK 3100 124 50 143 Randall Matos RMATOS 650.121.2874 7/30/1987 ST_CLERK 2600 124 50 144 Peter Vargas PVARGAS 650.121.2004 7/31/1987 ST_CLERK 2500 124 50 145 John Russell JRUSSEL 011.44.1344. 8/1/1987 SA_MAN 14000 0.4 100 80 146 Karen Partners KPARTNER 011.44.1344. 8/2/1987 SA_MAN 13500 0.3 100 80 147 Alberto Errazuriz AERRAZUR 011.44.1344. 8/3/1987 SA_MAN 12000 0.3 100 80 148 Gerald Cambrault GCAMBRAU 011.44.1344. 8/4/1987 SA_MAN 11000 0.3 100 80 149 Eleni Zlotkey EZLOTKEY 011.44.1344. 8/5/1987 SA_MAN 10500 0.2 100 80 150 Peter Tucker PTUCKER 011.44.1344. 8/6/1987 SA_REP 10000 0.3 145 80 151 David Bernstein DBERNSTE 011.44.1344. 8/7/1987 SA_REP 9500 0.25 145 80 152 Peter Hall PHALL 011.44.1344. 8/8/1987 SA_REP 9000 0.25 145 80 153 Christophe Olsen COLSEN 011.44.1344. 8/9/1987 SA_REP 8000 0.2 145 80 154 Nanette Cambrault NCAMBRAU 011.44.1344. 8/10/1987 SA_REP 7500 0.2 145 80 155 Oliver Tuvault OTUVAULT 011.44.1344. 8/11/1987 SA_REP 7000 0.15 145 80 156 Janette King JKING 011.44.1345. 8/12/1987 SA_REP 10000 0.35 146 80 157 Patrick Sully PSULLY 011.44.1345. 8/13/1987 SA_REP 9500 0.35 146 80 158 Allan McEwen AMCEWEN 011.44.1345. 8/14/1987 SA_REP 9000 0.35 146 80 159 Lindsey Smith LSMITH 011.44.1345. 8/15/1987 SA_REP 8000 0.3 146 80 160 Louise Doran LDORAN 011.44.1345. 8/16/1987 SA_REP 7500 0.3 146 80 161 Sarath Sewall SSEWALL 011.44.1345. 8/17/1987 SA_REP 7000 0.25 146 80 162 Clara Vishney CVISHNEY 011.44.1346. 8/18/1987 SA_REP 10500 0.25 147 80 163 Danielle Greene DGREENE 011.44.1346. 8/19/1987 SA_REP 9500 0.15 147 80 164 Mattea Marvins MMARVINS 011.44.1346. 8/20/1987 SA_REP 7200 0.1 147 80 165 David Lee DLEE 011.44.1346. 8/21/1987 SA_REP 6800 0.1 147 80 166 Sundar Ande SANDE 011.44.1346. 8/22/1987 SA_REP 6400 0.1 147 80 167 Amit Banda ABANDA 011.44.1346. 8/23/1987 SA_REP 6200 0.1 147 80 168 Lisa Ozer LOZER 011.44.1343. 8/24/1987 SA_REP 11500 0.25 148 80 169 Harrison Bloom HBLOOM 011.44.1343. 8/25/1987 SA_REP 10000 0.2 148 80 170 Tayler Fox TFOX 011.44.1343. 8/26/1987 SA_REP 9600 0.2 148 80 171 William Smith WSMITH 011.44.1343. 8/27/1987 SA_REP 7400 0.15 148 80 172 Elizabeth Bates EBATES 011.44.1343. 8/28/1987 SA_REP 7300 0.15 148 80 173 Sundita Kumar SKUMAR 011.44.1343. 8/29/1987 SA_REP 6100 0.1 148 80 174 Ellen Abel EABEL 011.44.1644. 8/30/1987 SA_REP 11000 0.3 149 80 175 Alyssa Hutton AHUTTON 011.44.1644. 8/31/1987 SA_REP 8800 0.25 149 80 176 Jonathon Taylor JTAYLOR 011.44.1644. 9/1/1987 SA_REP 8600 0.2 149 80 177 Jack Livingston JLIVINGS 011.44.1644. 9/2/1987 SA_REP 8400 0.2 149 80 178 Kimberely Grant KGRANT 011.44.1644. 9/3/1987 SA_REP 7000 0.15 149 179 Charles Johnson CJOHNSON 011.44.1644. 9/4/1987 SA_REP 6200 0.1 149 80 180 Winston Taylor WTAYLOR 650.507.9876 9/5/1987 SH_CLERK 3200 120 50 181 Jean Fleaur JFLEAUR 650.507.9877 9/6/1987 SH_CLERK 3100 120 50 182 Martha Sullivan MSULLIVA 650.507.9878 9/7/1987 SH_CLERK 2500 120 50 183 Girard Geoni GGEONI 650.507.9879 9/8/1987 SH_CLERK 2800 120 50 184 Nandita Sarchand NSARCHAN 650.509.1876 9/9/1987 SH_CLERK 4200 121 50 185 Alexis Bull ABULL 650.509.2876 9/10/1987 SH_CLERK 4100 121 50 186 Julia Dellinger JDELLING 650.509.3876 9/11/1987 SH_CLERK 3400 121 50 187 Anthony Cabrio ACABRIO 650.509.4876 9/12/1987 SH_CLERK 3000 121 50 188 Kelly Chung KCHUNG 650.505.1876 9/13/1987 SH_CLERK 3800 122 50 189 Jennifer Dilly JDILLY 650.505.2876 9/14/1987 SH_CLERK 3600 122 50 190 Timothy Gates TGATES 650.505.3876 9/15/1987 SH_CLERK 2900 122 50 191 Randall Perkins RPERKINS 650.505.4876 9/16/1987 SH_CLERK 2500 122 50 192 Sarah Bell SBELL 650.501.1876 9/17/1987 SH_CLERK 4000 123 50 193 Britney Everett BEVERETT 650.501.2876 9/18/1987 SH_CLERK 3900 123 50 194 Samuel McCain SMCCAIN 650.501.3876 9/19/1987 SH_CLERK 3200 123 50 195 Vance Jones VJONES 650.501.4876 9/20/1987 SH_CLERK 2800 123 50 196 Alana Walsh AWALSH 650.507.9811 9/21/1987 SH_CLERK 3100 124 50 197 Kevin Feeney KFEENEY 650.507.9822 9/22/1987 SH_CLERK 3000 124 50 198 Donald OConnell DOCONNEL 650.507.9833 9/23/1987 SH_CLERK 2600 124 50 199 Douglas Grant DGRANT 650.507.9844 9/24/1987 SH_CLERK 2600 124 50 200 Jennifer Whalen JWHALEN 515.123.4444 9/25/1987 AD_ASST 4400 101 10 201 Michael Hartstein MHARTSTE 515.123.5555 9/26/1987 MK_MAN 13000 100 20 202 Pat Fay PFAY 603.123.6666 9/27/1987 MK_REP 6000 201 20 203 Susan Mavris SMAVRIS 515.123.7777 9/28/1987 HR_REP 6500 101 40 204 Hermann Baer HBAER 515.123.8888 9/29/1987 PR_REP 10000 101 70 205 Shelley Higgins SHIGGINS 515.123.8080 9/30/1987 AC_MGR 12000 101 110 206 William Gietz WGIETZ 515.123.8181 10/1/1987 AC_ACCOUNT 8300 205 110
SQL Code:
-- This SQL query calculates the total salary for each combination of department and job, and displays the department code, job ID, and total salary.
-- SELECT statement begins
SELECT
department_id "Department Code", -- Selects the 'department_id' column and renames it as 'Department Code' in the result set
job_id, -- Selects the 'job_id' column
SUM(salary) "Total Salary" -- Calculates the sum of salaries for each combination of department and job, and renames the result column as 'Total Salary'
FROM
employees -- Specifies the 'employees' table to retrieve data from
GROUP BY
department_id, -- Groups the result set by the 'department_id' column
job_id; -- Groups the result set further by the 'job_id' column
Explanation:
- This SQL code is a SELECT statement that retrieves data from the 'employees' table.
- It calculates the total salary for each combination of department and job and displays the department code, job ID, and total salary.
- The SELECT clause selects the 'department_id' column from the 'employees' table and renames it as 'Department Code' in the result set.
- It also selects the 'job_id' column to display the job ID in the result set.
- The SUM(salary) function calculates the sum of salaries for each combination of department and job.
- The GROUP BY clause groups the result set first by the 'department_id' column and then further by the 'job_id' column, ensuring that calculations are performed for each unique combination.
- The query retrieves the department code, job ID, and total salary for each combination of department and job from the 'employees' table.
Output:
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
The following query displays the department code, total salary paid to employees group by department_id and manager_id=103.
Sample table: employees
employee_id first_name last_name email phone_number hire_date job_id salary commission_pct manager_id department_id ----------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- -------------- ---------- ------------- 100 Steven King SKING 515.123.4567 6/17/1987 AD_PRES 24000 90 101 Neena Kochhar NKOCHHAR 515.123.4568 6/18/1987 AD_VP 17000 100 90 102 Lex De Haan LDEHAAN 515.123.4569 6/19/1987 AD_VP 17000 100 90 103 Alexander Hunold AHUNOLD 590.423.4567 6/20/1987 IT_PROG 9000 102 60 104 Bruce Ernst BERNST 590.423.4568 6/21/1987 IT_PROG 6000 103 60 105 David Austin DAUSTIN 590.423.4569 6/22/1987 IT_PROG 4800 103 60 106 Valli Pataballa VPATABAL 590.423.4560 6/23/1987 IT_PROG 4800 103 60 107 Diana Lorentz DLORENTZ 590.423.5567 6/24/1987 IT_PROG 4200 103 60 108 Nancy Greenberg NGREENBE 515.124.4569 6/25/1987 FI_MGR 12000 101 100 109 Daniel Faviet DFAVIET 515.124.4169 6/26/1987 FI_ACCOUNT 9000 108 100 110 John Chen JCHEN 515.124.4269 6/27/1987 FI_ACCOUNT 8200 108 100 111 Ismael Sciarra ISCIARRA 515.124.4369 6/28/1987 FI_ACCOUNT 7700 108 100 112 Jose Manue Urman JMURMAN 515.124.4469 6/29/1987 FI_ACCOUNT 7800 108 100 113 Luis Popp LPOPP 515.124.4567 6/30/1987 FI_ACCOUNT 6900 108 100 114 Den Raphaely DRAPHEAL 515.127.4561 7/1/1987 PU_MAN 11000 100 30 115 Alexander Khoo AKHOO 515.127.4562 7/2/1987 PU_CLERK 3100 114 30 116 Shelli Baida SBAIDA 515.127.4563 7/3/1987 PU_CLERK 2900 114 30 117 Sigal Tobias STOBIAS 515.127.4564 7/4/1987 PU_CLERK 2800 114 30 118 Guy Himuro GHIMURO 515.127.4565 7/5/1987 PU_CLERK 2600 114 30 119 Karen Colmenares KCOLMENA 515.127.4566 7/6/1987 PU_CLERK 2500 114 30 120 Matthew Weiss MWEISS 650.123.1234 7/7/1987 ST_MAN 8000 100 50 121 Adam Fripp AFRIPP 650.123.2234 7/8/1987 ST_MAN 8200 100 50 122 Payam Kaufling PKAUFLIN 650.123.3234 7/9/1987 ST_MAN 7900 100 50 123 Shanta Vollman SVOLLMAN 650.123.4234 7/10/1987 ST_MAN 6500 100 50 124 Kevin Mourgos KMOURGOS 650.123.5234 7/11/1987 ST_MAN 5800 100 50 125 Julia Nayer JNAYER 650.124.1214 7/12/1987 ST_CLERK 3200 120 50 126 Irene Mikkilinen IMIKKILI 650.124.1224 7/13/1987 ST_CLERK 2700 120 50 127 James Landry JLANDRY 650.124.1334 7/14/1987 ST_CLERK 2400 120 50 128 Steven Markle SMARKLE 650.124.1434 7/15/1987 ST_CLERK 2200 120 50 129 Laura Bissot LBISSOT 650.124.5234 7/16/1987 ST_CLERK 3300 121 50 130 Mozhe Atkinson MATKINSO 650.124.6234 7/17/1987 ST_CLERK 2800 121 50 131 James Marlow JAMRLOW 650.124.7234 7/18/1987 ST_CLERK 2500 121 50 132 TJ Olson TJOLSON 650.124.8234 7/19/1987 ST_CLERK 2100 121 50 133 Jason Mallin JMALLIN 650.127.1934 7/20/1987 ST_CLERK 3300 122 50 134 Michael Rogers MROGERS 650.127.1834 7/21/1987 ST_CLERK 2900 122 50 135 Ki Gee KGEE 650.127.1734 7/22/1987 ST_CLERK 2400 122 50 136 Hazel Philtanker HPHILTAN 650.127.1634 7/23/1987 ST_CLERK 2200 122 50 137 Renske Ladwig RLADWIG 650.121.1234 7/24/1987 ST_CLERK 3600 123 50 138 Stephen Stiles SSTILES 650.121.2034 7/25/1987 ST_CLERK 3200 123 50 139 John Seo JSEO 650.121.2019 7/26/1987 ST_CLERK 2700 123 50 140 Joshua Patel JPATEL 650.121.1834 7/27/1987 ST_CLERK 2500 123 50 141 Trenna Rajs TRAJS 650.121.8009 7/28/1987 ST_CLERK 3500 124 50 142 Curtis Davies CDAVIES 650.121.2994 7/29/1987 ST_CLERK 3100 124 50 143 Randall Matos RMATOS 650.121.2874 7/30/1987 ST_CLERK 2600 124 50 144 Peter Vargas PVARGAS 650.121.2004 7/31/1987 ST_CLERK 2500 124 50 145 John Russell JRUSSEL 011.44.1344. 8/1/1987 SA_MAN 14000 0.4 100 80 146 Karen Partners KPARTNER 011.44.1344. 8/2/1987 SA_MAN 13500 0.3 100 80 147 Alberto Errazuriz AERRAZUR 011.44.1344. 8/3/1987 SA_MAN 12000 0.3 100 80 148 Gerald Cambrault GCAMBRAU 011.44.1344. 8/4/1987 SA_MAN 11000 0.3 100 80 149 Eleni Zlotkey EZLOTKEY 011.44.1344. 8/5/1987 SA_MAN 10500 0.2 100 80 150 Peter Tucker PTUCKER 011.44.1344. 8/6/1987 SA_REP 10000 0.3 145 80 151 David Bernstein DBERNSTE 011.44.1344. 8/7/1987 SA_REP 9500 0.25 145 80 152 Peter Hall PHALL 011.44.1344. 8/8/1987 SA_REP 9000 0.25 145 80 153 Christophe Olsen COLSEN 011.44.1344. 8/9/1987 SA_REP 8000 0.2 145 80 154 Nanette Cambrault NCAMBRAU 011.44.1344. 8/10/1987 SA_REP 7500 0.2 145 80 155 Oliver Tuvault OTUVAULT 011.44.1344. 8/11/1987 SA_REP 7000 0.15 145 80 156 Janette King JKING 011.44.1345. 8/12/1987 SA_REP 10000 0.35 146 80 157 Patrick Sully PSULLY 011.44.1345. 8/13/1987 SA_REP 9500 0.35 146 80 158 Allan McEwen AMCEWEN 011.44.1345. 8/14/1987 SA_REP 9000 0.35 146 80 159 Lindsey Smith LSMITH 011.44.1345. 8/15/1987 SA_REP 8000 0.3 146 80 160 Louise Doran LDORAN 011.44.1345. 8/16/1987 SA_REP 7500 0.3 146 80 161 Sarath Sewall SSEWALL 011.44.1345. 8/17/1987 SA_REP 7000 0.25 146 80 162 Clara Vishney CVISHNEY 011.44.1346. 8/18/1987 SA_REP 10500 0.25 147 80 163 Danielle Greene DGREENE 011.44.1346. 8/19/1987 SA_REP 9500 0.15 147 80 164 Mattea Marvins MMARVINS 011.44.1346. 8/20/1987 SA_REP 7200 0.1 147 80 165 David Lee DLEE 011.44.1346. 8/21/1987 SA_REP 6800 0.1 147 80 166 Sundar Ande SANDE 011.44.1346. 8/22/1987 SA_REP 6400 0.1 147 80 167 Amit Banda ABANDA 011.44.1346. 8/23/1987 SA_REP 6200 0.1 147 80 168 Lisa Ozer LOZER 011.44.1343. 8/24/1987 SA_REP 11500 0.25 148 80 169 Harrison Bloom HBLOOM 011.44.1343. 8/25/1987 SA_REP 10000 0.2 148 80 170 Tayler Fox TFOX 011.44.1343. 8/26/1987 SA_REP 9600 0.2 148 80 171 William Smith WSMITH 011.44.1343. 8/27/1987 SA_REP 7400 0.15 148 80 172 Elizabeth Bates EBATES 011.44.1343. 8/28/1987 SA_REP 7300 0.15 148 80 173 Sundita Kumar SKUMAR 011.44.1343. 8/29/1987 SA_REP 6100 0.1 148 80 174 Ellen Abel EABEL 011.44.1644. 8/30/1987 SA_REP 11000 0.3 149 80 175 Alyssa Hutton AHUTTON 011.44.1644. 8/31/1987 SA_REP 8800 0.25 149 80 176 Jonathon Taylor JTAYLOR 011.44.1644. 9/1/1987 SA_REP 8600 0.2 149 80 177 Jack Livingston JLIVINGS 011.44.1644. 9/2/1987 SA_REP 8400 0.2 149 80 178 Kimberely Grant KGRANT 011.44.1644. 9/3/1987 SA_REP 7000 0.15 149 179 Charles Johnson CJOHNSON 011.44.1644. 9/4/1987 SA_REP 6200 0.1 149 80 180 Winston Taylor WTAYLOR 650.507.9876 9/5/1987 SH_CLERK 3200 120 50 181 Jean Fleaur JFLEAUR 650.507.9877 9/6/1987 SH_CLERK 3100 120 50 182 Martha Sullivan MSULLIVA 650.507.9878 9/7/1987 SH_CLERK 2500 120 50 183 Girard Geoni GGEONI 650.507.9879 9/8/1987 SH_CLERK 2800 120 50 184 Nandita Sarchand NSARCHAN 650.509.1876 9/9/1987 SH_CLERK 4200 121 50 185 Alexis Bull ABULL 650.509.2876 9/10/1987 SH_CLERK 4100 121 50 186 Julia Dellinger JDELLING 650.509.3876 9/11/1987 SH_CLERK 3400 121 50 187 Anthony Cabrio ACABRIO 650.509.4876 9/12/1987 SH_CLERK 3000 121 50 188 Kelly Chung KCHUNG 650.505.1876 9/13/1987 SH_CLERK 3800 122 50 189 Jennifer Dilly JDILLY 650.505.2876 9/14/1987 SH_CLERK 3600 122 50 190 Timothy Gates TGATES 650.505.3876 9/15/1987 SH_CLERK 2900 122 50 191 Randall Perkins RPERKINS 650.505.4876 9/16/1987 SH_CLERK 2500 122 50 192 Sarah Bell SBELL 650.501.1876 9/17/1987 SH_CLERK 4000 123 50 193 Britney Everett BEVERETT 650.501.2876 9/18/1987 SH_CLERK 3900 123 50 194 Samuel McCain SMCCAIN 650.501.3876 9/19/1987 SH_CLERK 3200 123 50 195 Vance Jones VJONES 650.501.4876 9/20/1987 SH_CLERK 2800 123 50 196 Alana Walsh AWALSH 650.507.9811 9/21/1987 SH_CLERK 3100 124 50 197 Kevin Feeney KFEENEY 650.507.9822 9/22/1987 SH_CLERK 3000 124 50 198 Donald OConnell DOCONNEL 650.507.9833 9/23/1987 SH_CLERK 2600 124 50 199 Douglas Grant DGRANT 650.507.9844 9/24/1987 SH_CLERK 2600 124 50 200 Jennifer Whalen JWHALEN 515.123.4444 9/25/1987 AD_ASST 4400 101 10 201 Michael Hartstein MHARTSTE 515.123.5555 9/26/1987 MK_MAN 13000 100 20 202 Pat Fay PFAY 603.123.6666 9/27/1987 MK_REP 6000 201 20 203 Susan Mavris SMAVRIS 515.123.7777 9/28/1987 HR_REP 6500 101 40 204 Hermann Baer HBAER 515.123.8888 9/29/1987 PR_REP 10000 101 70 205 Shelley Higgins SHIGGINS 515.123.8080 9/30/1987 AC_MGR 12000 101 110 206 William Gietz WGIETZ 515.123.8181 10/1/1987 AC_ACCOUNT 8300 205 110
SQL Code:
-- This SQL query calculates the total salary for each department where the manager ID is 103, and displays the department code and total salary.
-- SELECT statement begins
SELECT
department_id "Department Code", -- Selects the 'department_id' column and renames it as 'Department Code' in the result set
SUM(salary) "Total Salary" -- Calculates the sum of salaries for each department where the manager ID is 103, and renames the result column as 'Total Salary'
FROM
employees -- Specifies the 'employees' table to retrieve data from
WHERE
MANAGER_ID = 103 -- Filters the rows where the manager ID is 103
GROUP BY
department_id; -- Groups the result set by the 'department_id' column
Explanation:
- This SQL code is a SELECT statement that retrieves data from the 'employees' table.
- It calculates the total salary for each department where the manager ID is 103 and displays the department code and total salary.
- The SELECT clause selects the 'department_id' column from the 'employees' table and renames it as 'Department Code' in the result set.
- The SUM(salary) function calculates the sum of salaries for each department where the manager ID is 103.
- The WHERE clause filters the rows where the manager ID is 103.
- The GROUP BY clause groups the result set by the 'department_id' column, ensuring that calculations are performed for each unique department.
- The query retrieves the department code and total salary for each department where the manager ID is 103 from the 'employees' table.
Output:
Department Code Total Salary
--------------- ------------
60 19800
SQL GROUP BY with HAVING clause
The following query displays the department id, number of employees of those groups that have more than 2 employees:
Sample table: employees
employee_id first_name last_name email phone_number hire_date job_id salary commission_pct manager_id department_id ----------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- -------------- ---------- ------------- 100 Steven King SKING 515.123.4567 6/17/1987 AD_PRES 24000 90 101 Neena Kochhar NKOCHHAR 515.123.4568 6/18/1987 AD_VP 17000 100 90 102 Lex De Haan LDEHAAN 515.123.4569 6/19/1987 AD_VP 17000 100 90 103 Alexander Hunold AHUNOLD 590.423.4567 6/20/1987 IT_PROG 9000 102 60 104 Bruce Ernst BERNST 590.423.4568 6/21/1987 IT_PROG 6000 103 60 105 David Austin DAUSTIN 590.423.4569 6/22/1987 IT_PROG 4800 103 60 106 Valli Pataballa VPATABAL 590.423.4560 6/23/1987 IT_PROG 4800 103 60 107 Diana Lorentz DLORENTZ 590.423.5567 6/24/1987 IT_PROG 4200 103 60 108 Nancy Greenberg NGREENBE 515.124.4569 6/25/1987 FI_MGR 12000 101 100 109 Daniel Faviet DFAVIET 515.124.4169 6/26/1987 FI_ACCOUNT 9000 108 100 110 John Chen JCHEN 515.124.4269 6/27/1987 FI_ACCOUNT 8200 108 100 111 Ismael Sciarra ISCIARRA 515.124.4369 6/28/1987 FI_ACCOUNT 7700 108 100 112 Jose Manue Urman JMURMAN 515.124.4469 6/29/1987 FI_ACCOUNT 7800 108 100 113 Luis Popp LPOPP 515.124.4567 6/30/1987 FI_ACCOUNT 6900 108 100 114 Den Raphaely DRAPHEAL 515.127.4561 7/1/1987 PU_MAN 11000 100 30 115 Alexander Khoo AKHOO 515.127.4562 7/2/1987 PU_CLERK 3100 114 30 116 Shelli Baida SBAIDA 515.127.4563 7/3/1987 PU_CLERK 2900 114 30 117 Sigal Tobias STOBIAS 515.127.4564 7/4/1987 PU_CLERK 2800 114 30 118 Guy Himuro GHIMURO 515.127.4565 7/5/1987 PU_CLERK 2600 114 30 119 Karen Colmenares KCOLMENA 515.127.4566 7/6/1987 PU_CLERK 2500 114 30 120 Matthew Weiss MWEISS 650.123.1234 7/7/1987 ST_MAN 8000 100 50 121 Adam Fripp AFRIPP 650.123.2234 7/8/1987 ST_MAN 8200 100 50 122 Payam Kaufling PKAUFLIN 650.123.3234 7/9/1987 ST_MAN 7900 100 50 123 Shanta Vollman SVOLLMAN 650.123.4234 7/10/1987 ST_MAN 6500 100 50 124 Kevin Mourgos KMOURGOS 650.123.5234 7/11/1987 ST_MAN 5800 100 50 125 Julia Nayer JNAYER 650.124.1214 7/12/1987 ST_CLERK 3200 120 50 126 Irene Mikkilinen IMIKKILI 650.124.1224 7/13/1987 ST_CLERK 2700 120 50 127 James Landry JLANDRY 650.124.1334 7/14/1987 ST_CLERK 2400 120 50 128 Steven Markle SMARKLE 650.124.1434 7/15/1987 ST_CLERK 2200 120 50 129 Laura Bissot LBISSOT 650.124.5234 7/16/1987 ST_CLERK 3300 121 50 130 Mozhe Atkinson MATKINSO 650.124.6234 7/17/1987 ST_CLERK 2800 121 50 131 James Marlow JAMRLOW 650.124.7234 7/18/1987 ST_CLERK 2500 121 50 132 TJ Olson TJOLSON 650.124.8234 7/19/1987 ST_CLERK 2100 121 50 133 Jason Mallin JMALLIN 650.127.1934 7/20/1987 ST_CLERK 3300 122 50 134 Michael Rogers MROGERS 650.127.1834 7/21/1987 ST_CLERK 2900 122 50 135 Ki Gee KGEE 650.127.1734 7/22/1987 ST_CLERK 2400 122 50 136 Hazel Philtanker HPHILTAN 650.127.1634 7/23/1987 ST_CLERK 2200 122 50 137 Renske Ladwig RLADWIG 650.121.1234 7/24/1987 ST_CLERK 3600 123 50 138 Stephen Stiles SSTILES 650.121.2034 7/25/1987 ST_CLERK 3200 123 50 139 John Seo JSEO 650.121.2019 7/26/1987 ST_CLERK 2700 123 50 140 Joshua Patel JPATEL 650.121.1834 7/27/1987 ST_CLERK 2500 123 50 141 Trenna Rajs TRAJS 650.121.8009 7/28/1987 ST_CLERK 3500 124 50 142 Curtis Davies CDAVIES 650.121.2994 7/29/1987 ST_CLERK 3100 124 50 143 Randall Matos RMATOS 650.121.2874 7/30/1987 ST_CLERK 2600 124 50 144 Peter Vargas PVARGAS 650.121.2004 7/31/1987 ST_CLERK 2500 124 50 145 John Russell JRUSSEL 011.44.1344. 8/1/1987 SA_MAN 14000 0.4 100 80 146 Karen Partners KPARTNER 011.44.1344. 8/2/1987 SA_MAN 13500 0.3 100 80 147 Alberto Errazuriz AERRAZUR 011.44.1344. 8/3/1987 SA_MAN 12000 0.3 100 80 148 Gerald Cambrault GCAMBRAU 011.44.1344. 8/4/1987 SA_MAN 11000 0.3 100 80 149 Eleni Zlotkey EZLOTKEY 011.44.1344. 8/5/1987 SA_MAN 10500 0.2 100 80 150 Peter Tucker PTUCKER 011.44.1344. 8/6/1987 SA_REP 10000 0.3 145 80 151 David Bernstein DBERNSTE 011.44.1344. 8/7/1987 SA_REP 9500 0.25 145 80 152 Peter Hall PHALL 011.44.1344. 8/8/1987 SA_REP 9000 0.25 145 80 153 Christophe Olsen COLSEN 011.44.1344. 8/9/1987 SA_REP 8000 0.2 145 80 154 Nanette Cambrault NCAMBRAU 011.44.1344. 8/10/1987 SA_REP 7500 0.2 145 80 155 Oliver Tuvault OTUVAULT 011.44.1344. 8/11/1987 SA_REP 7000 0.15 145 80 156 Janette King JKING 011.44.1345. 8/12/1987 SA_REP 10000 0.35 146 80 157 Patrick Sully PSULLY 011.44.1345. 8/13/1987 SA_REP 9500 0.35 146 80 158 Allan McEwen AMCEWEN 011.44.1345. 8/14/1987 SA_REP 9000 0.35 146 80 159 Lindsey Smith LSMITH 011.44.1345. 8/15/1987 SA_REP 8000 0.3 146 80 160 Louise Doran LDORAN 011.44.1345. 8/16/1987 SA_REP 7500 0.3 146 80 161 Sarath Sewall SSEWALL 011.44.1345. 8/17/1987 SA_REP 7000 0.25 146 80 162 Clara Vishney CVISHNEY 011.44.1346. 8/18/1987 SA_REP 10500 0.25 147 80 163 Danielle Greene DGREENE 011.44.1346. 8/19/1987 SA_REP 9500 0.15 147 80 164 Mattea Marvins MMARVINS 011.44.1346. 8/20/1987 SA_REP 7200 0.1 147 80 165 David Lee DLEE 011.44.1346. 8/21/1987 SA_REP 6800 0.1 147 80 166 Sundar Ande SANDE 011.44.1346. 8/22/1987 SA_REP 6400 0.1 147 80 167 Amit Banda ABANDA 011.44.1346. 8/23/1987 SA_REP 6200 0.1 147 80 168 Lisa Ozer LOZER 011.44.1343. 8/24/1987 SA_REP 11500 0.25 148 80 169 Harrison Bloom HBLOOM 011.44.1343. 8/25/1987 SA_REP 10000 0.2 148 80 170 Tayler Fox TFOX 011.44.1343. 8/26/1987 SA_REP 9600 0.2 148 80 171 William Smith WSMITH 011.44.1343. 8/27/1987 SA_REP 7400 0.15 148 80 172 Elizabeth Bates EBATES 011.44.1343. 8/28/1987 SA_REP 7300 0.15 148 80 173 Sundita Kumar SKUMAR 011.44.1343. 8/29/1987 SA_REP 6100 0.1 148 80 174 Ellen Abel EABEL 011.44.1644. 8/30/1987 SA_REP 11000 0.3 149 80 175 Alyssa Hutton AHUTTON 011.44.1644. 8/31/1987 SA_REP 8800 0.25 149 80 176 Jonathon Taylor JTAYLOR 011.44.1644. 9/1/1987 SA_REP 8600 0.2 149 80 177 Jack Livingston JLIVINGS 011.44.1644. 9/2/1987 SA_REP 8400 0.2 149 80 178 Kimberely Grant KGRANT 011.44.1644. 9/3/1987 SA_REP 7000 0.15 149 179 Charles Johnson CJOHNSON 011.44.1644. 9/4/1987 SA_REP 6200 0.1 149 80 180 Winston Taylor WTAYLOR 650.507.9876 9/5/1987 SH_CLERK 3200 120 50 181 Jean Fleaur JFLEAUR 650.507.9877 9/6/1987 SH_CLERK 3100 120 50 182 Martha Sullivan MSULLIVA 650.507.9878 9/7/1987 SH_CLERK 2500 120 50 183 Girard Geoni GGEONI 650.507.9879 9/8/1987 SH_CLERK 2800 120 50 184 Nandita Sarchand NSARCHAN 650.509.1876 9/9/1987 SH_CLERK 4200 121 50 185 Alexis Bull ABULL 650.509.2876 9/10/1987 SH_CLERK 4100 121 50 186 Julia Dellinger JDELLING 650.509.3876 9/11/1987 SH_CLERK 3400 121 50 187 Anthony Cabrio ACABRIO 650.509.4876 9/12/1987 SH_CLERK 3000 121 50 188 Kelly Chung KCHUNG 650.505.1876 9/13/1987 SH_CLERK 3800 122 50 189 Jennifer Dilly JDILLY 650.505.2876 9/14/1987 SH_CLERK 3600 122 50 190 Timothy Gates TGATES 650.505.3876 9/15/1987 SH_CLERK 2900 122 50 191 Randall Perkins RPERKINS 650.505.4876 9/16/1987 SH_CLERK 2500 122 50 192 Sarah Bell SBELL 650.501.1876 9/17/1987 SH_CLERK 4000 123 50 193 Britney Everett BEVERETT 650.501.2876 9/18/1987 SH_CLERK 3900 123 50 194 Samuel McCain SMCCAIN 650.501.3876 9/19/1987 SH_CLERK 3200 123 50 195 Vance Jones VJONES 650.501.4876 9/20/1987 SH_CLERK 2800 123 50 196 Alana Walsh AWALSH 650.507.9811 9/21/1987 SH_CLERK 3100 124 50 197 Kevin Feeney KFEENEY 650.507.9822 9/22/1987 SH_CLERK 3000 124 50 198 Donald OConnell DOCONNEL 650.507.9833 9/23/1987 SH_CLERK 2600 124 50 199 Douglas Grant DGRANT 650.507.9844 9/24/1987 SH_CLERK 2600 124 50 200 Jennifer Whalen JWHALEN 515.123.4444 9/25/1987 AD_ASST 4400 101 10 201 Michael Hartstein MHARTSTE 515.123.5555 9/26/1987 MK_MAN 13000 100 20 202 Pat Fay PFAY 603.123.6666 9/27/1987 MK_REP 6000 201 20 203 Susan Mavris SMAVRIS 515.123.7777 9/28/1987 HR_REP 6500 101 40 204 Hermann Baer HBAER 515.123.8888 9/29/1987 PR_REP 10000 101 70 205 Shelley Higgins SHIGGINS 515.123.8080 9/30/1987 AC_MGR 12000 101 110 206 William Gietz WGIETZ 515.123.8181 10/1/1987 AC_ACCOUNT 8300 205 110
SQL Code:
-- This SQL query counts the number of employees in each department and displays the department ID along with the count,
-- but only for departments with more than two employees.
-- SELECT statement begins
SELECT
department_id, -- Selects the 'department_id' column
count(*) "No. of Employee" -- Counts the number of rows for each department and renames the result column as 'No. of Employee'
FROM
employees -- Specifies the 'employees' table to retrieve data from
GROUP BY
department_id -- Groups the result set by the 'department_id' column
HAVING
count(*) > 2; -- Filters the grouped results to include only departments with more than two employees
Explanation:
- This SQL code is a SELECT statement that retrieves data from the 'employees' table.
- It counts the number of employees in each department and displays the department ID along with the count.
- The SELECT clause selects the 'department_id' column from the 'employees' table.
- The count(*) function counts the number of rows for each department.
- The GROUP BY clause groups the result set by the 'department_id' column, ensuring that calculations are performed for each unique department.
- The HAVING clause filters the grouped results to include only departments with more than two employees.
- The query retrieves the department ID and the count of employees for each department from the 'employees' table, but only for departments with more than two employees.
Output:
DEPARTMENT_ID No. of Employee
------------- ---------------
100 6
30 6
90 3
50 45
80 34
60 5
Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/sql/group-by.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics