SQL - Aggregate Functions and Examples
Aggregate functions in SQL are used to perform the calculation on data. These functions are inbuilt in SQL and return a single value.
SQL Aggregate Functions Usage:
Generally from a business perspective, Top levels managers are usually interested in knowing whole figures and not necessary the individual details.
>Aggregate functions allow us to easily produce summarized data from our database.
For instance, say from a 'sales' database, management may require following reports
- Least sold product variants.
- Most sold product variants.
- Average number that each product is sold in a month.
We easily produce above reports using aggregate functions.
The ISO standard defines five (5) aggregate functions namely;
In any interview, on SQL topic, you can expect atleast one question from the aggregate functions,
so lets first understand each of them and then look at few examples
1) The COUNT function returns the total number of values in the specified field. It works on both numeric and non-numeric data types. All aggregate functions by default exclude nulls values before working on the data.
2) MySQL SUM function which returns the sum of all the values in the specified column. SUM works on numeric fields only. Null values are excluded from the result returned.
3) MySQL AVG function returns the average of the values in a specified column. Just like the SUM function, it works only on numeric data types.
4) The MIN function returns the smallest value in the specified table field.
5) Just as the name suggests, the MAX function is the opposite of the MIN function. It returns the largest value from the specified table field.
Consider an orders table with the following structure.
Now, based on the above concepts there can be many types of questions like:
1. Count the number of orders made by a customer with CustomerName Smith made.
SELECT COUNT (*) FROM Sales WHERE CustomerName = 'Smith
2. In the above we used a filter condition, question could also be like, what is the count of total no of orders, for that we can simply use,
SELECT COUNT(*) FROM Sales
3.What if we are interested in the total value of all the orders?
We can use, SELECT SUM(OrderPrice) FROM Sales
4. More detailed analysis could be like, what is the average number of items per order?
We can use, SELECT AVG(OrderQuantity) FROM Sales
Note, we can combine all the above results with filters, what if I need average order price when the price is more than 200.
SELECT AVG(OrderPrice) FROM Sales WHERE OrderPrice > 200
5.minimum price paid for any of the orders
SELECT MIN(OrderPrice) FROM Sales
6. maximum paid for any of the orders
SELECT MAX(OrderPrice) FROM Sales
This was a brief intro into aggregate functions. All the very best.
know more about Basic SQL Interview Questions and Answers
Saturday, 21 April 2018 15:47
Hi There,Comment Link
Muchas Gracias Mi Amigo! You make learning so effortless. Anyone can follow you and I would not mind following you to the moon coz I know you are like my north star.
We have full and transaction log backups stored on a shared unc path. I am looking for a way to delete files older than 15 days to free up space.
I would also like to schedule it as job so it happens automatically.
I look forward to see your next updates.