Meritshot Tutorials
SQL Tutorial
-
SQL SyntaxSQL Syntax
-
SQL ORDER BY ClauseSQL ORDER BY Clause
-
Introduction to SQLIntroduction to SQL
-
SQL SELECT DISTINCTSQL SELECT DISTINCT
-
SQL Logical OperatorsSQL Logical Operators
-
SQL WHERE ClauseSQL WHERE Clause
-
SQL SELECT StatementSQL SELECT Statement
-
SQL DELETE StatementSQL DELETE Statement
-
SQL INSERT INTOSQL INSERT INTO
-
SQL Null ValuesSQL Null Values
-
SQL Update StatementSQL Update Statement
-
SQL Select TopSQL Select Top
-
SQL Aggregate FunctionsSQL Aggregate Functions
-
SQL LIKE and WildcardsSQL LIKE and Wildcards
-
SQL IN and SQL BETWEENSQL IN and SQL BETWEEN
-
SQL JOINSSQL JOINS
-
SQL Group BySQL Group By
-
SQL HavingSQL Having
-
SQL EXISTSSQL EXISTS
-
SQL SELECT INTOSQL SELECT INTO
-
SQL INSERT INTO SELECTSQL INSERT INTO SELECT
-
SQL CASE STATEMENTSQL CASE STATEMENT
-
SQL NULL FunctionsSQL NULL Functions
-
SQL Stored ProceduresSQL Stored Procedures
-
SQL User-Defined FunctionsSQL User-Defined Functions
-
SQL CommentsSQL Comments
-
SQL OperatorsSQL Operators
-
SQL Database Creation and ManagementSQL Database Creation and Management
-
SQL CREATE DATABASE StatementSQL CREATE DATABASE Statement
-
SQL CREATE TABLE StatementSQL CREATE TABLE Statement
-
SQL DROP DATABASE StatementSQL DROP DATABASE Statement
-
SQL DROP TABLE StatementSQL DROP TABLE Statement
-
SQL ALTER TABLE StatementSQL ALTER TABLE Statement
-
SQL NOT NULL ConstraintSQL NOT NULL Constraint
-
SQL UNIQUE ConstraintSQL UNIQUE Constraint
-
SQL PRIMARY KEY ConstraintSQL PRIMARY KEY Constraint
-
SQL FOREIGN KEY ConstraintSQL FOREIGN KEY Constraint
-
SQL CHECK ConstraintSQL CHECK Constraint
-
SQL DEFAULT ConstraintSQL DEFAULT Constraint
-
SQL IndexesSQL Indexes
-
SQL Date FunctionsSQL Date Functions
-
SQL ViewsSQL Views
-
SQL InjectionSQL Injection
-
SQL Data Types OverviewSQL Data Types Overview
-
SQL AUTO_INCREMENTSQL AUTO_INCREMENT
-
SQL Keywords ReferenceSQL Keywords Reference
SQL Aggregate Functions
Aggregate functions are used to perform calculations on multiple rows and return a single value that summarizes the data. They are commonly used in conjunction with the GROUP BY clause to perform calculations on each group of rows.
COUNT()
Purpose: Counts the number of rows in a dataset or the number of non-NULL values in a specific column.
Syntax:
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
Example:
1 Count Total Number of Employees:
This query returns the total number of employees in the employees table.
SELECT COUNT(*)
FROM employees;
Output:
COUNT(*) |
20 |
2 Count Employees in a Specific Department:
This query returns the number of employees in the ‘Sales’ department.
SELECT COUNT(*)
FROM employees
WHERE department = ‘Sales’;
Output:
COUNT(*) |
5 |
3 Count Employees with Salary Above 50,000:
This query counts the number of employees whose salary is greater than 50,000.
SELECT COUNT(*)
FROM employees
WHERE salary > 50000;
Output:
COUNT(*) |
10 |
4 Count Distinct Departments:
This query returns the number of distinct departments in the employees table.
SELECT COUNT(DISTINCT department)
FROM employees;
Output:
COUNT(DISTINCT department) |
4 |
2. SUM()
Purpose: Calculates the total sum of a numeric column.
Syntax:
SELECT SUM(column_name)
FROM table_name
WHERE condition;
Example:
1 Sum of All Salaries:
This query returns the total sum of all salaries in the employees table.
SELECT SUM(salary)
FROM employees;
Output:
SUM(salary) |
800000 |
2 Sum of Salaries for a Specific Department:
This query calculates the total salary for employees in the ‘IT’ department.
SELECT SUM(salary)
FROM employees
WHERE department = ‘IT’;
Output:
SUM(salary) |
200000 |
3 Sum of Salaries for Employees Above 30 Years Old:
This query sums the salaries of employees older than 30 years.
SELECT SUM(salary)
FROM employees
WHERE age > 30;
Output:
SUM(salary) |
350000 |
4 Sum of Salaries in the Last Year:
This query returns the total salary of employees hired in the last year.
SELECT SUM(salary)
FROM employees
WHERE hire_date > DATEADD(year, -1, GETDATE());
Output:
SUM(salary) |
120000 |
120000 3. AVG()
Purpose: Computes the average value of a numeric column.
Syntax:
SELECT AVG(column_name)
FROM table_name
WHERE condition;
Example:
Get the top 5 youngest employees from the employees table who are in the ‘IT’ department.
1 Average Salary of All Employees:
This query returns the average salary of all employees.
SELECT AVG(salary)
FROM employees;
Output:
AVG(salary) |
40000 |
2 Average Salary in a Specific Department:
This query calculates the average salary for employees in the ‘Finance’ department.
SELECT AVG(salary)
FROM employees
WHERE department = ‘Finance’;
Output:
AVG(salary) |
55000 |
3 Average Age of Employees:
This query returns the average age of all employees.
SELECT AVG(age)
FROM employees;
Output:
AVG(age) |
32 |
3 Average Salary for Employees with More Than 5 Years of Experience:
This query computes the average salary of employees with more than 5 years of experience.
SELECT AVG(salary)
FROM employees
WHERE experience > 5;
Output:
AVG(salary) |
45000 |
120000 3. AVG()
Purpose: Returns the smallest value from a numeric column.
Syntax:
SELECT MIN(column_name)
FROM table_name
WHERE condition;
1 Minimum Salary:
This query returns the minimum salary from the employees table.
SELECT MIN(salary)
FROM employees;
Output:
MIN(salary) |
30000 |
2 Minimum Age of Employees:
This query retrieves the minimum age of employees.
SELECT MIN(age)
FROM employees;
Output:
MIN(age) |
25 |
3 Minimum Salary in the ‘Sales’ Department:
This query finds the lowest salary in the ‘Sales’ department.
SELECT MIN(salary)
FROM employees
WHERE department = ‘Sales’;
Output:
MIN(salary) |
35000 |
4 Minimum Salary of Employees Hired in the Last Year:
This query returns the minimum salary of employees hired in the past year.
SELECT MIN(salary)
FROM employees
WHERE hire_date > DATEADD(year, -1, GETDATE());
Output:
MIN(salary) |
32000 |
5. MAX()
Purpose: Returns the largest value from a numeric column.
Syntax:
SELECT MAX(column_name)
FROM table_name
WHERE condition;
1 Maximum Salary:
This query returns the maximum salary from the employees table.
SELECT MAX(salary)
FROM employees;
Output:
MAX(salary) |
70000 |
2 Maximum Age of Employees:
This query retrieves the maximum age of employees.
SELECT MAX(age)
FROM employees;
Output:
MAX(age) |
60 |
3 Maximum Salary in the ‘IT’ Department:
This query finds the highest salary in the ‘IT’ department.
SELECT MAX(salary)
FROM employees
WHERE department = ‘IT’;
Output:
MAX(salary) |
70000 |
4 Maximum Salary of Employees with More Than 10 Years of Experience:
This query returns the highest salary of employees with more than 10 years of experience.
SELECT MAX(salary)
FROM employees
WHERE experience > 10;
Output:
MAX(salary) |
60000 |
Using Aggregate Functions with GROUP BY
Aggregate functions are often used with the GROUP BY clause to perform calculations on each group of rows.
Syntax:
SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name;
Calculate the total salary for each department:
SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department;
Output:
department | total_salary |
IT | 200000 |
Sales | 150000 |
Finance | 100000 |
Tips to Remember
- Combine with GROUP BY: Use GROUP BY to aggregate data based on different groups or categories.
- Handle NULL Values: Aggregate functions ignore NULL values, so be aware of how they affect your results.
- Performance: Aggregate functions can be resource-intensive, especially on large datasets. Ensure proper indexing and query optimization.
- Using HAVING: Use the HAVING clause to filter results after aggregation. It is similar to WHERE but operates on aggregated data.
Power of Aggregate Functions
Aggregate functions:
- Summarize Data: Provide a summary of data across multiple rows, helping in data analysis and reporting.
- Support Grouping: Allow you to perform calculations on subsets of data defined by GROUP BY.
- Enhance Analysis: Facilitate complex queries and data insights by summarizing large volumes of data.
Frequently Asked Questions
Q1: Can I use multiple aggregate functions in a single query?
A1: Yes, you can use multiple aggregate functions in a single query to get various summary values.
Example:
SELECT department, COUNT(*) AS num_employees, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;
Q2: How do I filter results based on aggregate functions?
A2: Use the HAVING clause to filter results after aggregation. The WHERE clause cannot be used with aggregate functions.
Example:
SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department
HAVING SUM(salary) > 100000;
Q3: Can I use aggregate functions with text columns?
A3: Aggregate functions like COUNT() can be used with text columns, but functions like SUM(), AVG(), MIN(), and MAX() are typically used with numeric columns.
Q4: What happens if there are no rows to aggregate?
A4: If there are no rows, aggregate functions will return NULL or zero, depending on the function.
Q5: How do I include rows with NULL values in aggregate functions?
A5: Aggregate functions ignore NULL values. To include rows with NULL values in calculations, consider using COALESCE to replace NULL with a default value.
