YogeshChauhan.com
How to Use Aggregate Functions (MIN, MAX, SUM, AVG, COUNT) to Summarize Data in SQL?
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
SyntaxWhat 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.

dreamhost

Leave a Reply

Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 How to set opacity or transparency using CSS? #3 Pagination in CSS with multiple examples #4 How to make HTML form interactive and using CSS? #5 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #6 How to uninstall Cocoapods from the Mac OS?

Recently Posted

Mar 3 How to embed YouTube or other video links in WordPress? Mar 3 How to change the Login Logo in WordPress? Mar 3 substring() Method in JavaScript Mar 3 Window setInterval() Method in JavaScript Mar 2 How to zoom an element on hover using CSS? Mar 2 the box-sizing property in CSS

You might also like these

How to verify your domain in Google Console with a TXT record?MiscSQL Right JoinSQL/MySQLHow to create HTML elements with ID and class using JavaScript?HTMLWhat are CSS Specificity Rules and how does browser apply them?CSSFETCH clause in PostgreSQLPostgresEXISTS and NOT EXISTS in PostgresPostgres