SOQL Conditional Counting?

Question

I am trying to run a query that will return counts of the different Opportunity Record Types for each user during a particular month. My query currently looks like this:

SELECT Owner.Name, COUNT(RecordType.Name) FROM Opportunity WHERE CALENDAR_MONTH(CreatedDate) = 1 AND CreatedDate = THIS_YEAR GROUP BY Owner.Name

This gets me the count of all opportunities for each person, but how can I break it down to the individual record types? FWIW, there are 5 record types, and for the purpose of this report, I only need 3 of them.

Answer 1

You can use the GROUP BY ROLLUP key phrase to get the number of opportunities by owner and by record type, with extra summary and grand total rows.

SELECT Owner.Name,  RecordType.Name, COUNT(Id)
FROM Opportunity 
WHERE CALENDAR_MONTH(CreatedDate) = 1 AND 
    CreatedDate = THIS_YEAR AND
    RecordType.Name IN ('Record Type A','Record Type B','Record Type C')
GROUP BY ROLLUP (Owner.Name, RecordType.Name)

This would have output like this:

John Doe    Record Type A     3
John Doe    Record Type B     2
John Doe    <<null>>          5
Jane Doe    Record Type A     4
Jane Doe    Record Type B     5
Jane Doe    <<null>>          9
<<null>>    <<null>>         14

Where the null rows indicate a subtotal, much like you'd see in a report on the reports tab. If you don't care for the summary rows, you can skip that and use GROUP BY Owner.Name, RecordType.Name instead.

Answer 2

you could group the results by ReportType.Name in your query, the query would then look like:

SELECT Owner.Name, COUNT(Id), RecordType.DeveloperName 
   FROM Opportunity WHERE CALENDAR_MONTH(CreatedDate) = 1 AND CreatedDate = THIS_YEAR 
   GROUP BY Owner.Name, RecordType.DeveloperName

This query would then give you the Count of Opportunities per person per record type filtered to the month specified.