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 How to check if radio button is checked or not using JavaScript? #2 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #3 How to add Read More Read Less Button using JavaScript? #4 How to uninstall Cocoapods from the Mac OS? #5 PHP Login System using PDO Part 1: Create User Registration Page #6 How to Use SQL MAX() Function with Dates?

Recently Posted

#Aug 15 Is PHP still good for back-end programming? #Aug 10 How to create a multisite network in WordPress? #Aug 3 How to create a circle that follows a cursor using JavaScript and CSS? #Aug 3 How to make a curtain slider using jQuery and CSS? #Aug 2 How to progressively load images and add a blurry placeholder? #Aug 1 How to create a placeholder loader (throbber) using CSS?
You might also like these
What is the difference between let and var in Swift?SwiftHow to add a scroll back to top button using JavaScript and CSS?CSSIs there a CSS parent selector?CSSHow destructuring works in React?ReactThe actual difference between indexOf() and search() in JavaScriptJavaScriptWordPress: How to print ACF array field values?WordPress