How to Use Aggregate Functions (MIN, MAX, SUM, AVG, COUNT) to Summarize Data in SQL?

In this article I'll show you some complex examples of aggregate functions. 

Aggregate functions are called column functions because they typically operate on the column values.

Check out the syntax of the aggregate functions:

NOTES:

• You can use MIN, MAX and COUNT with numeric, string or date values but to use AVG and SUM, the values need to be numeric values
• If you don't want to consider duplicate values, use DISTINCT
• Aggregate functions are mostly used with Group By clause 

Learn more about Group By in the Following Article

SQL GROUP BY Statement

Let's look at some complex examples.

//Query 1

SELECT COUNT(*) AS Total_Invoices, SUM(invoice_total - payment_total - credit_total) AS Total_Due FROM invoices
WHERE invoice_total - payment_total - credit_total >0;

//Query 2

SELECT 'After 01/14/2017' AS Selection_date, COUNT(*) AS Number_of_invoices, ROUND(AVG(invoice_total),2) AS Average_invoice_amt, SUM(invoice_total) AS Total_invoice_amt FROM invoices WHERE invoice_date > '2017-01-14';

//Query 3

SELECT 'After 01/14/2017' AS Selection_date, COUNT(*) AS Number_of_invoices, MAX(invoice_total) AS Highest_invoice_total, MIN(invoice_total) AS Lowest_invoice_total FROM invoices WHERE invoice_date > '2017-01-14';

Take a look at the DEMO from the link provided at the end of this article to better understand this concept.

All of the above queries use COUNT(*) function to count the number of rows in invoices table for the specific condition mentioned in the WHERE clause. In the second and third query, only invoices with dates after 2017-01-14 will be included in the count.

The first query will count the unpaid invoices and will also calculate the total due amount.

The second query uses AVG function to calculate average for those invoices and SUM function to make a total of those invoices.

The third query uses MIN and MAX to calculate highest and lowest amount of those invoices.

In the third query we use, MIN and MAX with numeric data. Let's use it with string and date in the following query.

//Query 4

SELECT MIN(seller_name) AS First_seller, MAX(seller_name) AS Last_seller, COUNT(seller_name) AS Number_of_sellers FROM sellers;

In the query above the MIN function will return sellers name which is lowest in the sorting and MAX returns the name which is highest in the sorting. As you have known by now, the COUNT function will count the total number of rows(number of vendors since there are no null values).

Let's look at the final query.

//Query 5

SELECT COUNT(DISTINCT seller_id) AS Number_of_sellers, COUNT(seller_id) AS Number_of_invoices, ROUND(AVG(invoice_total),2) AS Avg_invoice_amt, SUM(invoice_total) AS Total_invoice_amt FROM invoices WHERE invoice_date > '2017-01-14';

I am using DISTINCT in this query. I have used DISTINCT keyword in one COUNT only just to see if my table has any null values. But the Number_of_invoices and Number_of_sellers have same value (14) so it doesn't have any null values.

Share this post