Scribd
Upload
7 views13 pages

SQL Aggregate

This document covers SQL aggregate functions, including AVG, MAX, MIN, SUM, and COUNT, and their usage in querying databases. It explains the ORDER BY, GROUP BY, and HAVING clauses, providing syntax examples and sample data from an EMPLOYEE table. The tutorial aims to help users understand how to compute and select data from relational databases using SQL.

Uploaded by

sdnsdf
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
7 views13 pages

SQL Aggregate

This document covers SQL aggregate functions, including AVG, MAX, MIN, SUM, and COUNT, and their usage in querying databases. It explains the ORDER BY, GROUP BY, and HAVING clauses, providing syntax examples and sample data from an EMPLOYEE table. The tutorial aims to help users understand how to compute and select data from relational databases using SQL.

Uploaded by

sdnsdf
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
Unit 2: Database Query using SQL Visit to website: [Link] [Link] Chapter 10: SQL- Aggregate Functions SQL Supports Functions Which Can Be Used To Compute and Select Numeric, Character and Date Columns of Relations OP 1 ORDER BY 2 GROUP BY 3 HAVING CLAUSE AGGREGATE FUNCTIONS 4 AVG() 5 MAX() 6 MIN() 7 SUM() 8 COUNT(*) 9 COUNT(Expr.) Page tof 12 Unit 2: Database Query using SQL Visit to website: [Link] [Link] Chapter 10: SQL- Aggregate Functions Bren ate unease seek et Se eee aie ese Ce ee oe Ot ee eae eet Ete In this tutorial we will use the following EMPLOYEE table is having the following records EMPNO ENAME JOB MGR HIREDATE SAL COMM. DEPTT 7369 JAMES CLERK — 7876 2006-02-18 1140000 400000 20 7866 CLARK ANALYST 7782 2007-0622 2940000 500000 20 7654 KING © MANAGER 7900 2003-04-19 6000000 400000 30 7782 JONES PRESIDENT 7900 2006-0921 9570000 4000.00 10 7788 ADAMS ANALYST 7782 2007-03-13 1320000 250000 20 7839 SCOTT CLERK 7876 2006.03.06 3600000 3000.00 10 7844 FORD MANAGER 7900 2007-01-12 3600000 300000 30 7900 BLAKE ANALYST 7782 2008-07-18 3420000 250000 30 7902 MARTIN SALESMAN 7900 2009-01-05 1500000 200000 20 7934 TURNER CLERK 7876 2004-11-30 1800000 6000.00 10 The ORDER BY clause is used to sort the data in ascending or descending order, based on one or more columns. « By default ORDER BY sorts the data in ascending order. e We can use the keyword DESC to sort the data in descending order and the keyword ASC to sort in ascending order. Syntax: SELECT * FROM table_name ORDER BY column_name ASC | DESC page 2 of 12 Unit 2: Database Query using SQL Visit to website: ttsi/ [eww [Link] Chapter 10: SQL- Aggregate Functions SQL> SELECT * FROM EMPLOYEE ORDER BY ENAME; " EMPNO_ENAME 4 + JOB MGR HIREDATE SAL COMM. DEPTT 7783 ADAMS ANALYST 7782 2007-03413 1320000 250000 20 7900 BLAKE ANALYST 7782 2009-07-18 3420000 260000 30. 7565 CLARK ANALYST 7782 2007-05-22 2340000 500000 20 Teas FORD MANAGER 7900 2007-01-12 3600000 300000 30 7363 JAMES CLERK §=— 7876 200602-18 1140000 400000 20 7782 JONES PRESIDENT 7900 20060821 9570000 400000 10 7654 KING MANAGER 7300 2003-04-18 6000000 400000 30 7902 MARTIN SALESMAN 7300 2008-01-05 1000.00 200000 20 7839 SCOTT CLERK ©7876 2005.03.06 3600000 300000 10, 7934 TURNER CLERK 7876 2008-11-30 1800000 60000010 SQL> SELECT * FROM EMPLOYEE ORDER BY ENAME DESC; EMPNO ENAME + JOB MGR MIREDATE__SAL__COMM_ DEPIT 7934 TURNER CLERK 7876 2004-11-30 1800000 600000 10 7839 SCOTT CLERK 7676 2005.03.05 3600000 300000 10 7902 MARTIN SALESMAN 7900 2003.01.05 1500000 200000 20, 7654 KING MANAGER 7900 2003-019 6000000 400000 30 7182 JONES PRESIDENT 7900 2005-0921 9570000 00000 10 7s) aes CLERK — 7876 2005.02.18 1140000 400000 20 7844 FORD MANAGER 7900 2007-01-12 3600009 300000 30, 7566 CLARK “ANALYST 7782 2007-0522 2540000 s00000 20, 7900 BLAKE ANALYST 7782 2009.07-18 3420000 250000 30, 7188 ADAMS ANALYST 7782 2007-0313 1320000 250000 20 ge SOFT thondcbe Unit 2: Database Query using SQL sit to website: rar Chapter 10: SQL- Aggregate Functions e The GROUP BY clause groups records into summary rows. GROUP BY returns one records for each group. GROUP BY typically also involves aggregates: COUNT, MAX, SUM, AVG, etc. ¢ GROUP BY can group by one or more columns. Syntax: SELECT column-names FROM table-name WHERE condition GROUP BY column-names Syntax with ORDER BY: SELECT column-names FROM table-name WHERE condition GROUP BY column-names ORDER BY column-names |ENAME — SUM(SAL) ADAMS 13200.00 jouaxe 3120000 CLARK 2400.00 > |ForD 3600000 +> liames 1140.00 JONES 96700.00 Kins 0000.00 This would produce the following result- juasernn 100000 scorr eoop.c0 TURNER 1800.00 Page 4 of 12 Unit 2: Database Query using SQL Visit to website: [Link] [Link] Chapter 10: SQL- Aggregate Functions HAVING CLAUSE: © HAVING filters records that work on summarized GROUP BY results. ¢ HAVING applies to summarized group records, whereas WHERE applies to individual records. © Only the groups that meet the HAVING criteria will be returned. ¢ HAVING requires that a GROUP BY clause is present. WHERE and HAVING can be in the same query. Syntax: SELECT column-names FROM table-name WHERE condition GROUP BY column-names HAVING condition ORDER BY column-names SQL> SELECT JOB, AVG(SAL), SUM(SAL) FROM employee Group By JOB HAVING JOB = 'CLERK'; JOB AVG(SAL) SUM(SAL) >/CLERK 21800.000000 65400.00 SQL> Select Job, Count(*) from employee . Group by job Having count (*)<3; Job MANAGER Count(*) 2 PRESIDENT 1 1 SALESMAN Page Sof 12 Unit 2: Database Query using SQL sit to website: rw thondcbe Chapter 10: SQL- Aggregate Functions SQL Aggregate functions Returns the average value Returns the largest value Returns the smallest value Returns the sum Count occurrence of non null values returned by expression Count all rows in the specified table The AVG () function returns the average value of a numeric column. Syntax: SELECT AVG (Column_Name)FROM Table_Name; Syntax with Group by: SELECT AVG (Column_Name)FROM Table_Name GROUP BY Comlumn_Name; AVG(Sal)_ |-- 34890.000000 ‘Visit to website: https /anuw [Link] a AVG(Sal) SQL> SELECT DEPTT, AVG(Sal) 10 49900.000000 FROM EMPLOYEE 20 17250.000000 GROUP BY DEPTT; 30 43400.000000 SQL > SELECT DEPTT, AVG(Sal) AS AvgSal FROM EMPLOYEE GROUP BY DEPTT DEPTT «+ AvgSal | ORDER BY DEPTT; 2) The MAX () Functio The MAX () function returns the largest value of the selected column. Syntax: SELECT MAX(Column_Name) FROM Table_Name; Syntax with GROUP BY: SELECT MAX(Column_Name) FROM Table_Name GROUP BY Comlumn_Name SQL > SELECT MAX(Sal) FROM EMPLOYEE Page 7 of 12 Visit to website Chapter 10: SQL- Aggregate Functions SQL > SELECT ENAME, DEPTT, MAX (SAL) FROM EMPLOYEE GROUP BY DEPTT ENAME 1 DEPTT MAX(SAL) JAMES 20 JONES 10 700.00 ORDER BY ENAME; KING 30 6000.00 3) The MIN () Function: The MIN () function returns the smallest value of the selected column. Syntax: SELECT MIN(Column_Name) FROM Table_Name; Syntax with GROUP BY: SELECT MIN(Column_Name) FROM Table_Name GROUP BY Comlumn_Name MIN(Sal) 1140.00 SQL > SELECT ENAME, DEPTT, SQL > SELECT MIN(Sal) FROM EMPLOYEE ENAME 21 DEPTT MAX(SAL) eee en mem a AMES 20 2940.00 GROUP BY DEPTT JONES 10 9670.00 QROER) BY) EMAME KING 30 6000000 Page 8 of 12 Unit 2: Database Query using SQL Visit to website: [Link] [Link] Chapter 10: SQL- Aggregate Functions 4) The SUM () Function: The SUM () function returns the total sum of a numeric column. Syntax: SELECT SUM(Column_Name) FROM Table_Name; Syntax with GRouP BY: SELECT SUM(Column_Name) FROM Table_Name GROUP BY Comlumn_Name SQL > SELECT SUM(SAL) FROM EMPLOYEE; SUM(SAL) 34! 00 SQL > SELECT JOB, SUM(SAL) ,COUNT(*) FROM EMPLOYEE GROUP BY JOB HAVING COUNT (*) <3; JOB SUM(SAL)__ COUNT(’) eceesesege| MANAGER — 96000.00 2 PRESIDENT 96700.00 1 SALESMAN _ 16000.00 1 Page 9 of 12 ‘Visit to website: https /unww [Link] Unit 2: Database Query using SQL Chapter 10: SQL- Aggregate Functions “The COUNT (*) function returns the total number of records in a table, counts NULL values also” Syntax: SELECT COUNT(*) FROM GROUP BY Column_Name HAVING Condition; COUNT(*) 10 JOB COUNT() > {IANAGER 2 PRESIDENT 1 SALESMAN 1 Page 10 of 12 thondcbe ito website rar ur Database Query using SQL Chapter 10: SQL- Aggregate Functions 6) The COUNT (Epr. ) Function: “The COUNT (Column_Name) function returns the number of values (NULL values will not be counted) of the specified column.” “The COUNT (DISTINCT column_name) function returns the number of distinct values of the specified column.” Syntax: SELECT COUNT(Column_Name) FROM GROUP BY Column_Name HAVING Condition; SELECT COUNT (ENAME) FROM EMPLOYEE; COUNT(ENAME) 10 SELECT JOB, COUNT(JOB) FROM EMPLOYEE WHERE JOB='CLERK' GROUP BY JOB; JOB | COUNT(JOB) CLERK 3 Page 11 of 12 Unit 2: Database Query using SQL Visit to website: [Link] [Link] Chapter 10: SQL- Aggregate Functions JOB COUNT(JOB) ANALYST 3 CLERK MANAGER 2 PRESIDENT 1 ‘SALESMAN 1 COUNT(DISTINCT DEPTT) 3 Page 12 0f 12,

You might also like