## SQL AGGREGATE FUNCTIONS – SUM

SQL has 5 different aggregate functions (MIN, MAX, COUNT, SUM & AVG). Here we will be looking at the SUM function. The SUM function returns the total sum value of a column in the table.

### SYNTAX –

SELECT SUM(column1) FROM table_name WHERE condition;

Usually, the aggregate functions are used in conjunction with the GROUP BY/ HAVING clause, which gives even more meaningful results. The syntax for the query using SUM function using GROUP BY / HAVING clause is mentioned below –

SELECT column1, SUM(column2) FROM table_name WHERE condition1 GROUP BY column1 HAVING condition2;

At first, these might look little complicated, but once we go over few examples, it will make more sense.

Illustration – Consider the below employee table in the database which stores the information pertaining to employees in ABC company –

ID | employee_name | age | salary | Department | manager_name |
---|---|---|---|---|---|

1 | Jimmy | 32 | 80000 | Sales | Vijay |

2 | James | 35 | 85000 | Sales | Vijay |

3 | Helen | 33 | 82000 | HR | Krish |

4 | Mary | 26 | 78000 | Quality | Warner |

5 | William | 33 | 90000 | HR | Krish |

6 | Vijay | 38 | 105000 | Sales | Roger |

7 | Krish | 38 | 105000 | HR | Roger |

Query 1 – What is the total salary earned by all employees of ABC company?

SELECT SUM(salary) FROM employee;

SUM(salary)

—————

625000

Query 2 – What is the total salary earned by all employees of ABC company who belongs to sales department?

SELECT SUM(salary) FROM employee WHERE department=’sales’;

SUM(salary)

—————

270000

Query 3 – What is the total salary earned by employees in ABC company by each department.

SELECT Department, SUM(salary) FROM employee GROUP BY Department;

Department | SUM(Salary) |
---|---|

Sales | 270000 |

HR | 277000 |

Quality | 78000 |

Query 4 – What is the total salary earned by employees in ABC company by each department that gets greater than 150000 as salary. (That is we are only interested in those departments which get a total salary of greater than 150000)

SELECT Department, SUM(salary) FROM employee GROUP BY Department HAVING SUM(salary) > 150000;

Department | SUM(Salary) |
---|---|

Sales | 270000 |

HR | 277000 |