Summary of the data contains aggregated values that help in data analysis at a broader level. For example, to analyse the sales, the users might want to view the average sales or total sales for a specified time period. The SQL Server provides aggregate functions to generate summarized data.
The users might also want to view the summarized data in different group based on specific criteria. For example, the users want to view the average sales data region-wise or product-wise. In such a case, the sales data of each region will be displayed together. You can group the data by using the GROUP BY clause of the SELECT statement. You can also use aggregate function to summarize data when grouping it.
The aggregate functions, on execution, summarize the values for a column or a group of columns, and produce a single value. The syntax of an aggregated function is:
SELECT aggregate_function ([ALL|DISTINCT] expression) FROM table_name
Where,
Avg Returns the average of values on a numeric expression, either all or distinct.
Count: Returns the number of values in an expression, either all or distinct. The count function also accepts (*) as its parameter, but it counts the number of rows returned by the query.
Min Returns the lowest value in the expression. The following SQL query retrieves the minimum value from the Rate column of the EmployeePayHistory table with a user-defined heading:
SELECT ‘Minimum Rate’ = min (Rate) FROM HumanResources.EmployeePayHistory
Max Return the highest value in the expression.
Sum Returns the sum total of values in a numeric expression, either all or distinct.
The users might also want to view the summarized data in different group based on specific criteria. For example, the users want to view the average sales data region-wise or product-wise. In such a case, the sales data of each region will be displayed together. You can group the data by using the GROUP BY clause of the SELECT statement. You can also use aggregate function to summarize data when grouping it.
Summarizing Data by Using Aggregate Functions
At times, you need to calculate the summarized values of a column based on a set of rows. For example, the salary of employees is stored in the Rate column of the EmployeePayHistory table and you need to calculate the average salary earned by the employees.The aggregate functions, on execution, summarize the values for a column or a group of columns, and produce a single value. The syntax of an aggregated function is:
SELECT aggregate_function ([ALL|DISTINCT] expression) FROM table_name
Where,
- All specifies that the aggregate function is applied to all the values in the specified column.
- DISTINCT specifies that the aggregate function is applied to only unique values in the specified column.
- expression specifies a column or an expression with operators.
Avg Returns the average of values on a numeric expression, either all or distinct.
Count: Returns the number of values in an expression, either all or distinct. The count function also accepts (*) as its parameter, but it counts the number of rows returned by the query.
Min Returns the lowest value in the expression. The following SQL query retrieves the minimum value from the Rate column of the EmployeePayHistory table with a user-defined heading:
SELECT ‘Minimum Rate’ = min (Rate) FROM HumanResources.EmployeePayHistory
Max Return the highest value in the expression.
Sum Returns the sum total of values in a numeric expression, either all or distinct.