Introduction
Hi there, Ninja! This blog will discuss some critical Aggregate Functions in SQL Server.
SQL Server is a relational database management system developed and maintained by Microsoft. Its primary duties are storing, retrieving, and altering data in response to requests from other software programs. SQL Server has appeared as one of the most significant Relational Database Management Systems (RDBMS) with many applications. Because it is such an important subject, technical interviews frequently include questions regarding SQL Server.
Do not worry if you are not familiar with SQL. You can check out our SQL articles, which will help you learn and comprehend SQL at all levels.
So without further ado, let's get to the SQL Server Aggregate functions.
SQL Server Aggregate Functions
An aggregate function uses one or more values to conduct a calculation, and it then returns a single value. The aggregate function is typically used with the SELECT statement's GROUP BY clause and HAVING clause.
Multiple calls to aggregate functions with the same input values always result in the same value being returned. They are also known as deterministic functions as a result.
It should be noticed that the expression cannot be a subquery and that the aggregate functions cannot be nested.
There are several aggregate functions available in SQL Server, however the following are the most used ones:
1. AVG: The non-NULL values in a set are averaged using the aggregate function AVG().
2. CHECKSUM_AGG: A checksum value is calculated using a set of rows via the CHECKSUM AGG() function.
3. COUNT: The COUNT() aggregate function returns a group's total number of rows, including NULL-valued rows.
4. COUNT_BIG: The COUNT BIG() aggregate function calculates the total number of BIGINT-data-type rows, including NULL-valued rows, in a group.
5. MAX: In a collection of non-NULL numbers, the MAX() aggregate function returns the highest value (maximum).
6. MIN: In a set of non-NULL numbers, the MIN() aggregate function returns the lowest value (minimum).
7. SUM: The sum of all non-NULL values in a set is what the SUM() aggregate function returns.
8. VAR: Based on a sample of the supplied population, the VAR() function returns the statistical variance of values in an expression.
9. VARP: The VARP() function, however, does so based on the entire data population and returns the statistical variance of values in an expression.
10. STDEV: Based on a sample of the data population, the STDEV() function returns the statistical standard deviation of all the values supplied in the expression.
SQL Server Aggregate Function Syntax
The syntax for using aggregate functions in MySQL is as follows:
aggregate_function_name(DISTINCT | ALL expression)
aggregate_function_name: This field identifies the aggregate function that will be used.
DISTINCT | ALL: When we wish to include the different values in the calculation, we utilize the DISTINCT modifier. When we want to calculate all values, including duplicates, we use the ALL modifiers. All aggregate functions employ the ALL modifier by default if we don't supply a modifier.
expression: It represents the columns of the table or an expression with many columns and arithmetic operations.
Aggregate Functions Example
Let's examine the database's most popular aggregate functions in detail. Here, we'll first construct an employee table so that all the aggregate functions may be shown.
Run the given below command to create a table for employees:
CREATE TABLE employees(
Name varchar(45) NOT NULL,
WORKING_DATE date,
WORKING_HOURS varchar(10),
SALARY INT
);
The following data will then be added to this table:
INSERT INTO employees VALUES
('Robbin', '2020-10-04', 9, 25000),
('Sam', '2020-10-04', 12, 65000),
('Kane', '2020-10-04', 13, 35000),
('David', '2020-10-04', 10, 45000),
('Ricky', '2020-10-04', 12, 30000),
('Marsh', '2020-10-04', 13, 35000);
Using the SELECT query, we can view the table records:
COUNT() Function
The total number of rows, including NULL values from the supplied expression, is returned by this function. Additionally, it can count all records by a given criterion and return 0 if no records match the criteria. Both number and non-numeric data formats are compatible with it.
The COUNT() function is used in the example below to return the total number of employees represented in the employees database.
Input
SELECT COUNT(*) AS total_employees FROM employees; Output
SUM() Function
The NON-NULL values in the supplied set are totaled up by this function. It returns NULL if there are no records in the result collection. Only numeric data types can be used with the SUM function.
The following example computes the total salary of all employees stored in the employees table using the SUM function:
Input
SELECT SUM(SALARY) AS total_salary FROM employees; Output
AVG() Function
The NON-NULL values supplied in the column are averaged using this function. Only numeric data types can be used with the AVG function.
The following example computes the average salary of the employees listed in the employees database using the AVG function:
Input
SELECT AVG(SALARY) AS "Average Salary" FROM employees; Output
MIN() Function
This function returns the minimum (lowest) value of the supplied column. It also only functions with numeric data types.
In the example below, the MIN function is used to retrieve the lowest wage of a worker from the employees table:
Input
SELECT MIN(SALARY) AS "Lowest Salary" FROM employees; Output
Must Read SQL Clauses
Frequently Asked Questions
What is the SELECT statement?
When using SQL, the SELECT operator selects data from a database. The information received is kept in a result table known as the result-set.
What is Cursor?
A control structure known as a database cursor enables the exploration of database records. Additionally, cursors make it easier to process data after traversal, including record retrieval, addition, and deletion. They can be considered a reference to a specific row among a group of rows.
What are Entities?
An easily recognizable real-world thing, tangible or intangible, can be an entity. Students, professors, employees, departments, and projects, for instance, can all be referred to as entities in a database for a college. Each entity has a few related characteristics that give it a name.
What is Data Integrity?
Any system that stores, processes, or retrieves data must be designed, implemented, and used to ensure the data's accuracy and consistency over its entire life cycle. When information is entered into an application or a database, integrity constraints are also defined to impose business rules on the data.
Can we use aggregate functions in the update statement?
An UPDATE statement's set list may not contain an aggregate. But SQL doesn't always concur that it ought to be straightforward. Let's build up a fabricated example utilizing data from the San Francisco Airport's airport gate information.
Conclusion
Finally, you have reached the article's conclusion. Congratulations!! You gained knowledge of SQL Server Date Functions in this blog. You looked at the different aggregate functions to handle the average and sum of data.
Are you eager to read more articles on SQL Server? Coding Ninjas cover you, so don't worry.
Also Read - TCL Commands In SQL
Please refer to our guided pathways on Code studio to learn more about DSA, Competitive Programming, JavaScript, System Design, etc. Enroll in our courses, and use the accessible sample exams and questions as a guide. For placement preparations, look at the interview experiences and interview package.
Please do upvote our blogs if you find them helpful and informative!
Happy learning!
8+ registered 