WikiGalaxy
SQL SUM Function
Purpose of SQL SUM:
The SQL SUM function is used to calculate the total sum of a numeric column. It is particularly useful in scenarios where you need to determine the total amount of sales, revenue, or any other numeric data stored in a database.
SELECT SUM(salary) AS TotalSalary
FROM employees;
Use Case:
This query calculates the total salary paid to all employees in the company. The result is stored in a column named 'TotalSalary'.
Console Output:
TotalSalary: 150000
SUM with GROUP BY
Purpose of GROUP BY with SUM:
The GROUP BY clause is used in collaboration with the SUM function to aggregate data across multiple records and group the results by one or more columns.
SELECT department, SUM(salary) AS TotalSalary
FROM employees
GROUP BY department;
Use Case:
This query calculates the total salary for each department in the company. The results are grouped by the department name.
Console Output:
Sales: 50000
Marketing: 40000
HR: 30000
SUM with WHERE Clause
Purpose of WHERE with SUM:
The WHERE clause is used to filter records before applying the SUM function, allowing you to calculate totals based on specific conditions.
SELECT SUM(salary) AS TotalSalary
FROM employees
WHERE department = 'Sales';
Use Case:
This query calculates the total salary for employees in the Sales department only.
Console Output:
TotalSalary: 50000
SUM with DISTINCT
Purpose of DISTINCT with SUM:
The DISTINCT keyword can be used with the SUM function to calculate the total of unique values, avoiding duplicates in the calculation.
SELECT SUM(DISTINCT salary) AS TotalUniqueSalary
FROM employees;
Use Case:
This query calculates the total of unique salary values across all employees, ensuring no duplicate salaries are summed.
Console Output:
TotalUniqueSalary: 130000
SUM with HAVING Clause
Purpose of HAVING with SUM:
The HAVING clause is used to filter records that are aggregated by the GROUP BY clause, allowing you to specify conditions on the aggregated data.
SELECT department, SUM(salary) AS TotalSalary
FROM employees
GROUP BY department
HAVING SUM(salary) > 30000;
Use Case:
This query calculates the total salary for each department but only returns departments where the total salary exceeds 30,000.
Console Output:
Sales: 50000
Marketing: 40000
SUM with JOINs
Purpose of SUM with JOINs:
Using SUM with JOINs allows you to calculate totals across related tables, providing more comprehensive insights by combining data from multiple sources.
SELECT e.department, SUM(o.amount) AS TotalSales
FROM employees e
JOIN orders o ON e.employee_id = o.employee_id
GROUP BY e.department;
Use Case:
This query calculates the total sales amount for each department by joining the employees and orders tables on employee_id.
Console Output:
Sales: 70000
Marketing: 50000
SUM with Subqueries
Purpose of SUM with Subqueries:
Subqueries allow you to use the result of one query as a condition in another query, providing a powerful way to calculate totals based on complex criteria.
SELECT department, SUM(salary) AS TotalSalary
FROM employees
WHERE department IN (SELECT department FROM departments WHERE location = 'New York')
GROUP BY department;
Use Case:
This query calculates the total salary for departments located in New York, using a subquery to filter departments by location.
Console Output:
Sales: 50000
SUM with Multiple Columns
Purpose of SUM with Multiple Columns:
Calculating the sum of multiple columns can provide insights into complex datasets, such as total expenses, revenue, and profit margins.
SELECT SUM(salary + bonus) AS TotalCompensation
FROM employees;
Use Case:
This query calculates the total compensation for employees by summing up their salary and bonus amounts.
Console Output:
TotalCompensation: 200000
