Aggregate Functions - SQL Query
Aggregate functions
SQL aggregate functions returns single value, calculated from values present in specific column.
Lets understand the following aggregate functions with an example.
Consider the following table titled 'Items'.
| ID | Name | Quantity | Price |
|---|
| 1 | Laptop | 2 | 70000 |
| 2 | Desktop | 3 | 60000 |
| 3 | Speakers | 5 | 15000 |
| 4 | UPS | 10 | 20000 |
i) Display the sum of 'Price' columns from table items.
SELECT SUM(Price) AS Total Price FROM Items
In the above table SUM() function performs a summation of column 'Price' and Alice (AS) is used to display Total Price.
Output:
ii) Display the average of 'Price' column from table 'Items'
SELECT AVG(Price) AS Average Price FROM Items
In the above statement AVG() function calculates average of column 'Price' and Alice (AS) is used to display Average Price.
Output:
Iii) Display the total records present in the table Items.
SELECT COUNT(*) FROM table Items
The COUNT(*) function returns the total number of records present in the table Items.
Output:
iv) Display the total records present in column 'ID' from table Items.
SELECT COUNT(ID) AS Item_ID FROM Items
In the above statement the function COUNT() calculates the total 'ID' as Item_ID.
Output:
v) Display the highest value in column 'Price' from table 'Items'.
SELECT MAX(Price) AS Highest_Price FROM Items
The MAX(Price) function returns the highest value present in the column and Alice(AS) is used to display the Highest_Price present in the column.
Output:
vi) Display the smallest value present in the column 'Price' from table 'Items'.
SELECT MIN(Price) AS Smallest_Price FROM Table Items
The MIN(Price) function returns the smallest value present the column 'Price'.
