In this post we will discuss below points:
- SQL Aggregate Functions
- Explanation of each with Example
- Group By Clause
- 10 SQL Queries
Aggregate Function
SQL aggregation function is used to perform the calculations on multiple rows of a single column of a table. It returns a single value.
Count()
COUNT function is used to Count the number of rows in a database table. It can work on both numeric and non-numeric data types.
Count(*): Returns total number of records . Count(salary): Return number of Non Null values over the column salary.
Sum()
Sum function is used to calculate the sum of all selected columns. It works on numeric fields only.
Example: Select SUM(Salary) from EmployeeDetail.
Avg()
The AVG function is used to calculate the average value of the numeric type. AVG function returns the average of all non-Null values.
Example: Select AVG(Salary) from EmployeeDetail.
Max()
MAX function is used to find the maximum value of a certain column. This function determines the largest value of all selected values of a column.
Example: Select MAX(Salary) from EmployeeDetail.
Max()
MAX function is used to find the maximum value of a certain column. This function determines the largest value of all selected values of a column.
Example: Select MAX(Salary) from EmployeeDetail.
Min()
MIN function is used to find the minimum value of a certain column. This function determines the smallest value of all selected values of a column.
Example: Select MIN(Salary) from EmployeeDetail.
Group By Keyword
The GROUP BY clause is a SQL command that is used to group rows that have the same values. The GROUP BY clause is used in the SELECT statement.
The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.
Example: SELECT column1, column2
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2
ORDER BY column1, column2
Sample Queries:
Get the highest "Salary" from EmployeeDetail table.
SELECT MAX(Salary) As MaxSalary FROM [EmployeeDetail]
Get the lowest "Salary" from EmployeeDetail table.
SELECT MIN(Salary) As MinSalary FROM [EmployeeDetail]
Get how many employee exist in "EmployeeDetail" table.
SELECT COUNT(*) FROM [EmployeeDetail]
Write the query to get the department and department wise total(sum) salary from "EmployeeDetail" table.
SELECT Department, SUM(Salary) AS [Total Salary] FROM [EmployeeDetail] GROUP BY Department
Write the query to get the department and department wise total(sum) salary, display it in ascending order according to salary.
SELECT Department, SUM(Salary) AS [Total Salary] FROM [EmployeeDetail] GROUP BY Department ORDER BY SUM(Salary) ASC
Write the query to get the department and department wise total(sum) salary, display it in descending order according to salary.
SELECT Department, SUM(Salary) AS [Total Salary] FROM [EmployeeDetail] GROUP BY Department ORDER BY SUM(Salary) DESC
Write the query to get the department, total no. of emp in each department, total(sum) salary with respect to department from "EmployeeDetail" table.
SELECT Department, COUNT(*) AS [Dept Counts], SUM(Salary) AS [Total Salary] FROM [EmployeeDetail] GROUP BY Department
Get department wise average salary from "EmployeeDetail" table order by salary ascending.
SELECT Department, AVG(Salary) AS [Average Salary] FROM [EmployeeDetail] GROUP BY Department ORDER BY AVG(Salary) ASC
Get department wise maximum salary from "EmployeeDetail" table order by salary ascending.
SELECT Department, MAX(Salary) AS [Average Salary] FROM [EmployeeDetail] GROUP BY Department ORDER BY MAX(Salary) ASC
Get department wise minimum salary from "EmployeeDetail" table order by salary ascending.
SELECT Department, MIN(Salary) AS [Average Salary] FROM [EmployeeDetail] GROUP BY Department ORDER BY MIN(Salary) ASC
Please refer below YouTube video for more detail explanation:
No comments:
Post a Comment