Skip to main content

Blog Archives

4.2.2 Aggregate Functions

Aggregate functions perform a calculation on a set of values and return a single value. Common aggregate functions include:

  • COUNT(): Counts the number of rows in each group.
  • SUM(): Adds up the values in a specific column for each group.
  • AVG(): Calculates the average of values in a specific column for each group.
  • MAX() and MIN(): Determine the highest and lowest values in a specific column for each group, respectively.
  • COUNT(DISTINCT): Counts the number of distinct (unique) values in a column for each group.

SQL aggregations operate similarly to Salesforce, so technically you already have built and deployed aggregate functions if you have ever used Salesforce summary reports or built Salesforce Rollup fields.

  1. To add your first aggregation field, add the following SQL COUNT aggregation function. COUNT(*) after COUNTRY in your SELECT statement as shown below:
SELECT "COUNTRY",COUNT(*) FROM SFDC.STAGE.LEAD

 GROUP BY "COUNTRY";

If you leave the COUNT function without an argument within the parenthesis, it will return an error. The wild card “*” within the COUNT(*) tells the count function to count all records in the query. Do you see anything wrong with the column names in the query result result?

ANSWER

It is missing a human readable column name. When you use aggregate functions you need to declare a field name alias because you are creating this as a new column in your query result. Let’s make the correction to our query and work on the next aggregation:

SELECT "COUNTRY",COUNT(*) as "Lead Count"

FROM SFDC.STAGE.LEAD

GROUP BY "COUNTRY";
  1. Count the number of statuses per country. Add another COUNT() function to count the number of “STATE” records.

COUNT(“STATE”)

Now, the result illustrates that there are states present for the USA but not for other countries. However, my intent was not to merely count the number of records with and without a state.