In this article we will look at how to use aggregate functions to summarize and analyze data. Before we begin, please note that you can access all the material related to the tasks mentioned in this article in my GitHub repository at https://github.com/isuri-balasooriya2/TheMathLab/. You can navigate to the SQL folder to see all the resources related to our SQL series. While you are there, please explore the other areas I’ve covered as well if you are interested :)
We will be using the same table and data created during the previous article (https://themathlab.hashnode.dev/exploring-sql-concepts-for-beginners).
Aggregate functions are used to perform calculations on a set of values and return a single result. We have COUNT, SUM, AVG, MIN, MAX to perform different tasks,
SELECT COUNT(*) FROM employees; -- Total employees
SELECT SUM(salary) FROM employees; -- Total salary
SELECT AVG(salary) FROM employees; -- Average salary
SELECT MAX(salary), MIN(salary) FROM employees; -- Highest & lowest salaries
The GROUP BY clause groups rows sharing a common value, allowing you to apply aggregate functions to each group. For example, if you wanted to find out the average salary per department, you can get the average salary using the AVG() function and use the GROUP BY clause to group rows with the same value for department.
SELECT department, AVG(salary) FROM employees GROUP BY department;
The next clause we are going to look at is HAVING for filtering. We have already talked about the WHERE clause for conditions, so you might this why do we need another clause called HAVING and why cant we use WHERE clause here as well. The things is WHERE clause doesn’t work with aggregated data. In simpler terms, while WHERE filters rows, HAVING filters groups created by GROUP BY. So if you want to add a condition after grouping with GROUP BY, you use HAVING. Lets say you want to find out which departments has more than two employees.
SELECT COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) >2
I think we did good on aggregation and grouping using SQL. In the next article we will look at joins and relationships in SQL.