SOQL 201 for Admins & Developers: Slice & Dice Your Org’s Data With Aggregate Queries, Neil Hohmann

Already know how to write a basic SOQL query? Great! But what about an *aggregate* SOQL query? You know, the kind that uses aggregate functions like COUNT & MAX along with GROUP BY and HAVING clauses? No? Well, get ready to learn how to slice & dice your org’s data right inside your own dev console. From finding duplicate records to prototyping summary & matrix reports, learn the ins and outs of aggregate queries during this fast-paced but admin-friendly session on advanced SOQL concepts.

1. Neil Hohmann SOQL 201 for Admins & Developers: Slice & Dice Your Org’s Data With Aggregate Queries

2. SOQL = Hacking Into Your Own Org

3. SOQL = Summoning Spell

4. Aggregate Functions GROUP BY GROUP BY ROLLUP GROUPING function Date Functions HAVING SOQL 201: Aggregate Queries

5. Developer Console > Query Editor

6. SELECT Id, Name, FORMAT(AnnualRevenue) FROM Account WHERE CreatedDate = THIS_MONTH SELECT COUNT(Id) FROM Account WHERE CreatedDate = THIS_MONTH Standard SOQL vs Aggregate SOQL

7. Standard SOQL vs Aggregate SOQL

8. SELECT Donor_Level__c, COUNT( Id ) , FORMAT( SUM( CC_Total_Given_Pledged__c ) ), FORMAT( AVG( CC_Total_Given_Pledged__c ) ) FROM Account WHERE CC_Total_Given_Pledged__c > 0 GROUP BY ROLLUP ( Donor_Level__c ) ORDER BY Donor_Level__c NULLS LAST Aggregate SOQL Example

9. Aggregate SOQL Example

10. SELECT Name, COUNT( Id ) FROM Opportunity GROUP BY Name HAVING COUNT(Id) > 1 Aggregate SOQL Example

11. Aggregate SOQL Example

12. Aggregate Functions

13. COUNT(), COUNT( fieldName ) COUNT_DISTINCT( fieldName ) SUM( fieldName ) AVG( fieldName ) MIN( fieldName ) MAX( fieldName ) Aggregate Functions

14. SELECT COUNT() FROM Account WHERE BillingState = 'CA' SELECT COUNT(Id) FROM Account WHERE BillingState = 'CA' COUNT() vs COUNT( fieldName )

15. COUNT() vs COUNT( fieldName )

16. COUNT() vs COUNT( fieldName )

17. COUNT() vs COUNT( fieldName )

18. SELECT CALENDAR_YEAR(MC4SF__MC_Campaign__r.MC4SF__Create_Time__c), CALENDAR_MONTH(MC4SF__MC_Campaign__r.MC4SF__Create_Time__c), COUNT_DISTINCT( MC4SF__MC_Campaign__r.Name ) , MC4SF__Action__c, COUNT( MC4SF__MC_Subscriber__c ) , COUNT_DISTINCT( MC4SF__MC_Subscriber__c ) FROM MC4SF__MC_Subscriber_Activity__c WHERE MC4SF__Action__c IN ('sent', 'open', 'click') GROUP BY CALENDAR_YEAR(MC4SF__MC_Campaign__r.MC4SF__Create_Time__c), CALENDAR_MONTH(MC4SF__MC_Campaign__r.MC4SF__Create_Time__c), MC4SF__Action__c ORDER BY CALENDAR_YEAR(MC4SF__MC_Campaign__r.MC4SF__Create_Time__c) DESC, CALENDAR_MONTH(MC4SF__MC_Campaign__r.MC4SF__Create_Time__c) DESC, MC4SF__Action__c DESC COUNT( fieldName ) vs COUNT_DISTINCT( fieldName )

19. COUNT( fieldName ) vs COUNT_DISTINCT( fieldName )

20. SELECT COUNT(Id), FORMAT( SUM( Amount ) ), AVG( Amount ) FROM Opportunity WHERE StageName = 'Closed Won' SUM( fieldName ) & AVG( fieldName )

21. SUM( fieldName ) & AVG( fieldName )

22. SUM( fieldName ) & AVG( fieldName )

23. SELECT COUNT(Id), FORMAT(SUM(Amount)), FORMAT(AVG(Amount)), FORMAT( MIN( Amount )) , FORMAT( MAX( Amount ) ) FROM Opportunity WHERE StageName = 'Closed Won' MIN ( fieldName ) & MAX( fieldName )

24. MIN ( fieldName ) & MAX( fieldName )

25. SELECT UserId, Status, COUNT(Id), FORMAT( MIN( LoginTime ) ), MAX( LoginTime ) FROM LoginHistory WHERE LoginTime = LAST_N_DAYS:21 GROUP BY UserId, Status MIN( fieldName ) & MAX( fieldName )

26. MIN( fieldName ) & MAX( fieldName )

27. SELECT MIN( LastName ) , MAX( LastName ) FROM Contact MIN( fieldName ) & MAX( fieldName )

28. MIN( fieldName ) & MAX( fieldName )

29. GROUP BY

30. SELECT StageName, COUNT(Id), SUM(Amount), MIN(Amount), MAX(Amount), AVG(Amount) FROM Opportunity GROUP BY StageName GROUP BY fieldName

31. GROUP BY fieldName

32. GROUP BY fieldName

33. GROUP BY fieldName

34. GROUP BY fieldName

35. Write an aggregate SOQL query that breaks down ‘Closed Won’ opportunities by account industry. Include the opportunity record count, as well as the sum and average of the amount for each industry. Be sure to format the currency fields. SOQL CHALLENGE #1 SELECT FROM WHERE GROUP BY

36. GROUP BY fieldName

37. GROUP BY ROLLUP

38. GROUP BY fieldName

39. SELECT StageName, COUNT(Id), FORMAT(SUM(Amount)), FORMAT(MIN(Amount)), FORMAT(MAX(Amount)), FORMAT(AVG(Amount)) FROM Opportunity GROUP BY ROLLUP ( StageName ) ORDER BY StageName NULLS LAST GROUP BY fieldName

40. GROUP BY ROLLUP(fieldName)

41. GROUP BY ROLLUP(fieldName)

42. GROUPING Function

43. SELECT StageName, GROUPING( StageName ) total, COUNT(Id), FORMAT(SUM(Amount)), FORMAT(MIN(Amount)), FORMAT(MAX(Amount)), FORMAT(AVG(Amount)) FROM Opportunity GROUP BY ROLLUP ( StageName ) ORDER BY StageName NULLS LAST GROUPING(fieldName)

44. GROUPING(fieldName)

45. GROUPING(fieldName)

46. Date Functions

47. Date Functions Date functions allow you to group and filter data by date periods (eg, day, calendar month, fiscal quarter, etc.): CALENDAR_MONTH() → returns a number 1 - 12 CALENDAR_YEAR() → returns the year DAY_IN_WEEK () → returns a number 1 - 7 FISCAL_YEAR() → returns the fiscal year FISCAL_QUARTER() → returns the fiscal quarter (1 - 4)

48. SELECT CALENDAR_YEAR( MC4SF__MC_Campaign__r.MC4SF__Create_Time__c ) , CALENDAR_MONTH( MC4SF__MC_Campaign__r.MC4SF__Create_Time__c ) , COUNT_DISTINCT(MC4SF__MC_Campaign__r.Name), MC4SF__Action__c, COUNT(MC4SF__MC_Subscriber__c), COUNT_DISTINCT(MC4SF__MC_Subscriber__c) FROM MC4SF__MC_Subscriber_Activity__c WHERE MC4SF__Action__c IN ('sent', 'open', 'click') GROUP BY CALENDAR_YEAR( MC4SF__MC_Campaign__r.MC4SF__Create_Time__c ) , CALENDAR_MONTH( MC4SF__MC_Campaign__r.MC4SF__Create_Time__c ) , MC4SF__Action__c ORDER BY CALENDAR_YEAR( MC4SF__MC_Campaign__r.MC4SF__Create_Time__c ) DESC , CALENDAR_MONTH( MC4SF__MC_Campaign__r.MC4SF__Create_Time__c ) DESC , MC4SF__Action__c DESC Date Functions : CALENDAR_YEAR(), CALENDAR_MONTH()

49. Date Functions: CALENDAR_YEAR(), CALENDAR_MONTH()

50. Write a query that breaks down ‘Closed Won’ opportunities by account owner, year, and month. Include the record count and the sum. Also, include the most recent opportunity close date. Include a roll-up summary field. Format all currency and date fields. SOQL CHALLENGE #2 SELECT FROM WHERE GROUP BY

51. SOQL CHALLENGE #2

52. HAVING

53. SELECT Account.Name, COUNT(Id) FROM Opportunity WHERE StageName = 'Closed Won' GROUP BY Account.Name HAVING Count(Id) >= 3 HAVING

54. HAVING

55. Write a query that catches duplicate contacts. It should only identify duplicates if both the name and email are found in another contact. It should return the name, email, and number of matches. Make sure it doesn’t return records where the email field is null or blank. SOQL CHALLENGE #3 SELECT FROM WHERE GROUP BY HAVING

56. SOQL CHALLENGE #3

57. Resources Salesforce SOQL and SOSL Reference (Spring ‘24) neilhohmann.com: SOQL 101 Part 3: Aggregate Queries CampApex.org (SOQL section) Coding with the Force (YouTube): SOQL & SOSL Guide Trailhead: SOQL for Admins > Use Bind Variables and Aggregate Functions