Yogesh Chauhan's Blog

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

in SQL/MySQL on December 31, 2019

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:

Syntax of aggregate functions
Syntax What it does
AVG([ALL or DISTINCT] expression) will give you average of all the non-null values in a column
SUM([ALL or DISTINCT] expression) will give you the total of all the non-null values in a column
MIN([ALL or DISTINCT] expression) will give you the lowest number in a column (non-null values)
MAX([ALL or DISTINCT] expression) will give you the highest number in a column (non-null values)
COUNT([ALL or DISTINCT] expression) will count the total number of rows in a column for non-null values only
COUNT(*) will count the total number of rows in a column (with null values rows)

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.


Most Read

#1 Solution to the error “Visual Studio Code can’t be opened because Apple cannot check it for malicious software” #2 How to add Read More Read Less Button using JavaScript? #3 How to check if radio button is checked or not using JavaScript? #4 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #5 How to uninstall Cocoapods from the Mac OS? #6 PHP Login System using PDO Part 1: Create User Registration Page

Recently Posted

#Apr 8 JSON.stringify() in JavaScript #Apr 7 Middleware in NextJS #Jan 17 4 advanced ways to search Colleague #Jan 16 Colleague UI Basics: The Search Area #Jan 16 Colleague UI Basics: The Context Area #Jan 16 Colleague UI Basics: Accessing the user interface
You might also like these
What is React? Learn the basicsReactThe 8 Golden Rules of Programming I Have Learned in My CareerMiscellaneousHow to get category name using post id in WordPress?WordPressA list of wp-cli commands to use via SSHWordPressWordPress: How to display fields from ACF Flexible Contents?WordPressMicroservices vs Monolithic ArchitectureMiscellaneous