l5 aggregate functions
DESCRIPTION
TRANSCRIPT
![Page 1: L5 aggregate functions](https://reader035.vdocuments.us/reader035/viewer/2022081414/54bd2d8c4a7959ea0f8b4820/html5/thumbnails/1.jpg)
Aggregate Functions
1Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh
![Page 2: L5 aggregate functions](https://reader035.vdocuments.us/reader035/viewer/2022081414/54bd2d8c4a7959ea0f8b4820/html5/thumbnails/2.jpg)
Aggregate Functions ISO standard defines five aggregate functions:1. COUNT returns number of values in specified
column.2. SUM returns sum of values in specified column.3. AVG returns average of values in specified column.4. MIN returns smallest value in specified column.5. MAX returns largest value in specified column.
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 2
![Page 3: L5 aggregate functions](https://reader035.vdocuments.us/reader035/viewer/2022081414/54bd2d8c4a7959ea0f8b4820/html5/thumbnails/3.jpg)
Things to remember
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 3
![Page 4: L5 aggregate functions](https://reader035.vdocuments.us/reader035/viewer/2022081414/54bd2d8c4a7959ea0f8b4820/html5/thumbnails/4.jpg)
Count Function The COUNT() function returns the number of rows that
matches a specified criteria. The COUNT(column_name) function returns the number of
values (NULL values will not be counted) of the specified column:
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 4
![Page 5: L5 aggregate functions](https://reader035.vdocuments.us/reader035/viewer/2022081414/54bd2d8c4a7959ea0f8b4820/html5/thumbnails/5.jpg)
Count Function
Now we want to count the number of orders from "Customer Nilsen".
We use the following SQL statement:
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 5
![Page 6: L5 aggregate functions](https://reader035.vdocuments.us/reader035/viewer/2022081414/54bd2d8c4a7959ea0f8b4820/html5/thumbnails/6.jpg)
Count Function The COUNT(*) function returns the number of records in a
table:
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 6
![Page 7: L5 aggregate functions](https://reader035.vdocuments.us/reader035/viewer/2022081414/54bd2d8c4a7959ea0f8b4820/html5/thumbnails/7.jpg)
Count Function
which is the total number of rows in the table.
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 7
![Page 8: L5 aggregate functions](https://reader035.vdocuments.us/reader035/viewer/2022081414/54bd2d8c4a7959ea0f8b4820/html5/thumbnails/8.jpg)
Count Function The COUNT(DISTINCT column_name) function returns the
number of distinct values of the specified column:
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 8
![Page 9: L5 aggregate functions](https://reader035.vdocuments.us/reader035/viewer/2022081414/54bd2d8c4a7959ea0f8b4820/html5/thumbnails/9.jpg)
Count Function
which is the number of unique customers (Hansen, Nilsen, and Jensen) in the "Orders" table.
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 9
![Page 10: L5 aggregate functions](https://reader035.vdocuments.us/reader035/viewer/2022081414/54bd2d8c4a7959ea0f8b4820/html5/thumbnails/10.jpg)
Sum Function
Now we want to find the sum of all "OrderPrice" fields". We use the following SQL statement:
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 10
![Page 11: L5 aggregate functions](https://reader035.vdocuments.us/reader035/viewer/2022081414/54bd2d8c4a7959ea0f8b4820/html5/thumbnails/11.jpg)
Avg Function
Now we want to find the average value of the "OrderPrice" fields.
We use the following SQL statement:
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 11
![Page 12: L5 aggregate functions](https://reader035.vdocuments.us/reader035/viewer/2022081414/54bd2d8c4a7959ea0f8b4820/html5/thumbnails/12.jpg)
Max Function
Now we want to find the largest value of the "OrderPrice" column.
We use the following SQL statement:
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 12
![Page 13: L5 aggregate functions](https://reader035.vdocuments.us/reader035/viewer/2022081414/54bd2d8c4a7959ea0f8b4820/html5/thumbnails/13.jpg)
Min Function
Now we want to find the smallest value of the "OrderPrice" column.
We use the following SQL statement:
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 13
![Page 14: L5 aggregate functions](https://reader035.vdocuments.us/reader035/viewer/2022081414/54bd2d8c4a7959ea0f8b4820/html5/thumbnails/14.jpg)
Group By Clause The GROUP BY statement is used in conjunction with the
aggregate functions to group the result-set by one or more columns.
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 14
![Page 15: L5 aggregate functions](https://reader035.vdocuments.us/reader035/viewer/2022081414/54bd2d8c4a7959ea0f8b4820/html5/thumbnails/15.jpg)
Group By Clause
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 15
Now we want to find the total sum (total order) of each customer.
We will have to use the GROUP BY statement to group the customers.
We use the following SQL statement:
![Page 16: L5 aggregate functions](https://reader035.vdocuments.us/reader035/viewer/2022081414/54bd2d8c4a7959ea0f8b4820/html5/thumbnails/16.jpg)
Group By Clause
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 16
![Page 17: L5 aggregate functions](https://reader035.vdocuments.us/reader035/viewer/2022081414/54bd2d8c4a7959ea0f8b4820/html5/thumbnails/17.jpg)
Group By Clause
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 17
If we omit the group by clause from the statement-
![Page 18: L5 aggregate functions](https://reader035.vdocuments.us/reader035/viewer/2022081414/54bd2d8c4a7959ea0f8b4820/html5/thumbnails/18.jpg)
Things to remember
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 18
![Page 19: L5 aggregate functions](https://reader035.vdocuments.us/reader035/viewer/2022081414/54bd2d8c4a7959ea0f8b4820/html5/thumbnails/19.jpg)
Having Clause The HAVING clause was added to SQL because the WHERE
keyword could not be used with aggregate functions.
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 19
![Page 20: L5 aggregate functions](https://reader035.vdocuments.us/reader035/viewer/2022081414/54bd2d8c4a7959ea0f8b4820/html5/thumbnails/20.jpg)
Having Clause
Now we want to find if any of the customers have a total order of less than 2000.
We use the following SQL statement:
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 20
![Page 21: L5 aggregate functions](https://reader035.vdocuments.us/reader035/viewer/2022081414/54bd2d8c4a7959ea0f8b4820/html5/thumbnails/21.jpg)
Having Clause
Now we want to find if the customers "Hansen" or "Jensen" have a total order of more than 1500.
We add an ordinary WHERE clause to the SQL statement:
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 21
![Page 22: L5 aggregate functions](https://reader035.vdocuments.us/reader035/viewer/2022081414/54bd2d8c4a7959ea0f8b4820/html5/thumbnails/22.jpg)
Things to remember A HAVING condition can refer only to an
expression in the SELECT list, or to an expression involving an aggregate function.
If you specify an expression in the HAVING clause that isn't in the SELECT list, or that isn't an aggregate expression, you will get an error.
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 22
![Page 23: L5 aggregate functions](https://reader035.vdocuments.us/reader035/viewer/2022081414/54bd2d8c4a7959ea0f8b4820/html5/thumbnails/23.jpg)
Reference World Wide Web Schools,
http://www.w3schools.com/sql/sql_functions.asp [05/04/2009]
Rushdi Shams, Lecturer, Dept of CSE, KUET, Bangladesh 23