Aggregate functions allow us to group multiple rows of data by some defining characteristic of the group, such as department, so that summary statistics about the group (averages, totals, maximums etc.) can be calculated. The output is a single value which is based on the set of values in the source data.

There are five aggregate functions namely; SUM, AVG, MIN, MAX and COUNT.

These aggregate functions are used in conjunction with the GROUP BY and HAVING clauses as part of our SELECT statement.

The GROUP BY clause will divide the rows of a table into groups that have identical values in one or more columns.

If the GROUP BY clause is omitted when an aggregate function is used, then the entire table is considered as one group, and the function displays a single value for the entire table.

The HAVING clause performs the same function as the WHERE clause, but with aggregate values. A query can contain both a WHERE clause and a HAVING clause.

Together, fits into the SELECT expression in the following manner.

SELECT column-name1 [,column-name2]
FROM table-name
WHERE search-condition
GROUP BY column-name1 [,column-name2]
HAVING some-condition
ORDER BY column-name1 [DESC] [,column-name2] [DESC]

Having vs. Where

The WHERE clause is applied first to the individual rows in the tables. Only the rows that meet the conditions in the WHERE clause go on to be grouped.

The HAVING clause is then applied to the rows in the result set to filter values in the GROUP BY clause. Only the groups that meet the HAVING conditions appear in the query output.

Breaking GROUP BY down conceptually

Examples

I am a certified Salesforce Marketing Cloud Consultant at Bower House Digital in Melbourne, Australia.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store