Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
michaellee.ampfmichaellee.ampf 

Group By Year(DateTime) in SOQL?

Hi,

 

I'm wondering if it is possible to do write SOQL that allows me to group by the year of a date-time value within an object.

 

I've simplified it for my posting here, but basically I've got a custom object Sale__c with two fields

 

Number(10,2)  AmountOfSale__c

DateTime         DateOfSale__c

 

I'd love to be able to do a SOQL query like the following:

 

[Select Year(DateOfSale__c) YearOfSale,

              SUM(AmountOfSale__c) TotalAmountOfSale

 from Sale__c s

 group by Year(DateOfSale__c)]

 

Something like this wouldn't work directly.  So then I thought about adding a formula field to the object that tried to get the year of the date of sale, but that didn't work either.  (As I get an error message that the formula field can't be grouped on. 

 

Obviously if I need to I can have the totalling done within Apex, and not the SOQL query, but I'd much rather have it done by SOQL instead so I wanted to see if anyone has any guidence or suggestions here.

 

Thanks,

Michael

 

 

Best Answer chosen by Admin (Salesforce Developers) 
Anup JadhavAnup Jadhav

Hi there,

 

Since the field is called 'DateOfSale__c', you could consider changing the field type from 'DateTime' to 'Date' if the time is not important or not required.

 

You can then write a query like this:

 

 

[Select Calendar_Year(DateOfSale__c) YearOfSale, SUM(AmountOfSale__c) TotalAmountOfSale from Sale__c s
GROUP BY Calendar_Year(DateOfSale__c)]

 

If you can't change the field 'DateOfSale__c' type to field to 'Date', then create another field of type 'Date', and setup a workflow rule which copies the date from 'DateOfSale__c' to this new field everytime the record is inserted or updated. You can then use this new field in the query.

 

 

Hope this helps!

 

AJ

All Answers

Anup JadhavAnup Jadhav

Hi there,

 

Since the field is called 'DateOfSale__c', you could consider changing the field type from 'DateTime' to 'Date' if the time is not important or not required.

 

You can then write a query like this:

 

 

[Select Calendar_Year(DateOfSale__c) YearOfSale, SUM(AmountOfSale__c) TotalAmountOfSale from Sale__c s
GROUP BY Calendar_Year(DateOfSale__c)]

 

If you can't change the field 'DateOfSale__c' type to field to 'Date', then create another field of type 'Date', and setup a workflow rule which copies the date from 'DateOfSale__c' to this new field everytime the record is inserted or updated. You can then use this new field in the query.

 

 

Hope this helps!

 

AJ

This was selected as the best answer
michaellee.ampfmichaellee.ampf

Yes, this helped!

 

I didn't want to change the DateOfSale__c type to Date, but I was able to create a formula field that calculated the Date value of DateOfSale__c, and then I was able to group by Calendar_Year on that field without problems.

 

 

 

 
Anup JadhavAnup Jadhav

I'm glad I could help. Beware of formula fields though. They hit salesforce servers very hard. And if you have loads of formula field which get modififed all the time, you can soon expect an email from Salesforce support. If the data set you are working with is small, and the related field isn't modified often, then you should be okay. But if it the dataset is large, and the related field is modified quite frequently then use the static field updated by workflow approach.