What are aggregate functions in SQL? What are those functions?
Define, syntax and an example with output for:
Aggregate functions in SQL are used to perform calculation on data. These functions are inbuilt in SQL and return a single value.
Define, syntax and an example with output for:
SUM functionSUM function returns the sum or addition of all NOT NULL values of a column.
For e.g. I have a Table employee with the fields id, name, salary and I want the sum of all salaries, I can use SUM function as shown
SELECT SUM(emp_salary) from employee;
Hence, if my column emp_salary has values 20,000, 22,000, 21,000; the output will be 63,000
AVG (average) function AVG function returns the average of all NOT NULL values of a column.
For e.g. I have a Table employee with the fields id, name, salary and I want the average of all salaries, I can use AVG function as shown
SELECT AVG(emp_salary) from employee;
Hence, if my column emp_salary has values 20,000, 22,000, 21,000; the output will be 21,000.
COUNT functionCOUNT function returns the number of rows or values of a table.
For e.g. I have a Table employee with the fields id, name, salary and I want the count of all rows, I can use COUNT function as shown
SELECT COUNT(*) from employee;
Max and Min function.MAX function returns the largest value of a column in a table.
For e.g. I have a Table employee with the fields id, name, salary and I want the maximum salary of an employee, I can use MAX function as shown
SELECT MAX(emp_salary) from employee;
Hence, if my column emp_salary has values 20,000, 22,000, 21,000; the output will be 22,000
MIN function returns the smallest value of a column in a table.
For e.g. I have a Table employee with the fields id, name, salary and I want the minimun salary of an employee, I can use MIN function as shown
SELECT MIN(emp_salary) from employee;
Hence, if my column emp_salary has values 20,000, 22,000, 21,000; the output will be 20,000.
Using ROLLUP to aggregate data in SQL
ROLLUP in SQL allows you summarize your data and view. I have a Table company with the fields dept, branch, number of employees and following is the data.
| Dept | Branch | Number |
| Dev | Seatle | 2000 |
| Analyst | Seatle | 1000 |
| Tester | St Louis | 400 |
| Dev | St Louis | 100 |
| Analyst | boston | 250 |
| dev | boston | 500 |
| Tester | boston | 560 |
On using a ROLLUP query,
SELECT Dept, Branch, SUM(Number) as Number FROM company GROUP BY dept,branch WITH ROLLUP
Will give results of state wise inventory of each branch.
