function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
ShotsterShotster 

Multiple COUNTs In One Query?

I have a custom object that logs events for each account. It's set up as a master-detail and works just fine. However, I'd like to extract the total number of events for this year and for this month. I have the following SOQL query:

SELECT COUNT()
FROM  EventLog__c
WHERE Account__c = 'XXXXXXXXXXXX'
AND Time_Stamp__c = THIS_MONTH

This works fine, but is there a way to get the count for the current year in the same query, or do I have to submit a 2nd query using THIS_YEAR?

(p.s. I'm using REST API to submit the query - not Apex - although I'm not sure it matters.)

-Steve
 
Best Answer chosen by Shotster
Suraj PSuraj P
The closest I can think of is grouping by month the logs for this year:

SELECT calendar_month(time_stamp__c) month,COUNT(id) logcount
FROM  EventLog__c
WHERE Account__c = 'XXXXXXXXXXXX'
AND Time_Stamp__c = THIS_YEAR group by calendar_month(time_stamp__c) order by calendar_month(time_stamp__c) desc

First row should give you the count for the current month. Sum up the counts to get the total count for the entire year.

All Answers

Suraj PSuraj P
The closest I can think of is grouping by month the logs for this year:

SELECT calendar_month(time_stamp__c) month,COUNT(id) logcount
FROM  EventLog__c
WHERE Account__c = 'XXXXXXXXXXXX'
AND Time_Stamp__c = THIS_YEAR group by calendar_month(time_stamp__c) order by calendar_month(time_stamp__c) desc

First row should give you the count for the current month. Sum up the counts to get the total count for the entire year.
This was selected as the best answer
ShotsterShotster
Thanks for the quick reply, Suraj. Works great!

One thing I don't quite understand is the purpose of "month" and "logcount". The query seems to work fine without them.
Suraj PSuraj P
Those are column aliases meant to help you retrieve the columns thus: rec.get('month') instead of rec.get('expr0')
If this answer helped you, please mark it as the best answer. 

Thanks
Suraj PSuraj P
Also, please be aware that if you don't have any logs for the current month, the first row of results will be count of logs for the previous month. Make sure you check that in your logic.