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
SF_MonkeySF_Monkey 

soql query with group clause

I am trying to write a soql quey that is very similar to a summary report. I unsure but I believe that I need to group clause in the query.
This is the result that I am trying to achieveUser-added image

every field on this table is from the same object. I need to group by download_cycle__c and portal_name__c.
here is what I have been written but i cant get the desired result. I have not even attempted to write the lightning component table for it. 
select count(Download_Cycle__c), Name, Last_Download__c, Portal_Name__c, count(Portal_URL__c), Password__c, Login__c, Count(Bill_Cycle__c), Location_Address__c, Location_Name__c from vendor_Invoice__c group by Name, Last_Download__c, Portal_Name__c, Password__c, Login__c, Location_Address__c, Location_Name__c


Thanks for the help in advance
Best Answer chosen by SF_Monkey
Alain CabonAlain Cabon
You just need an ordered SOQL query (there is no sum here) and the trick is to fill wrappers and lists (apex) according the block datatables used in the VFP or nested Json data for a lightning tree-grid for lightning / lwc (javascript).

https://developer.salesforce.com/docs/component-library/bundle/lightning:treeGrid/example
https://www.soliantconsulting.com/blog/multi-tiered-tables-in-visualforce/ 
select Download_Cycle__c, Name, Last_Download__c, Portal_Name__c, Password__c, Login__c, Location_Address__c, Location_Name__c from vendor_Invoice__c
order by Download_Cycle__c, Portal_Name__c, Name

All Answers

Gabriel C FerreiraGabriel C Ferreira

Hi,

You have to order the fields in the Group By clause as the groups you want to have:

Group by count(Download_Cycle__c), Portal_Name__c, Name ....
If this helped solve your issue, please mark as the best answer.

Best Regards
SF_MonkeySF_Monkey
select count(Download_Cycle__c), Name, Last_Download__c, Portal_Name__c, Portal_URL__c, Password__c, Login__c, Bill_Cycle__c,  Location_Address__c, Location_Name__c
                from vendor_Invoice__c group by count(Download_Cycle__c),Name, Last_Download__c, Portal_Name__c, Portal_URL__c, Password__c, Login__c, Bill_Cycle__c,  Location_Address__c, Location_Name__c

I tried this and I got error: "Only date aggregate functions are allowed as grouping expressions: count"
Gabriel C FerreiraGabriel C Ferreira
Try to remove the Count() and leave only the field on the group by. Note that "count(Download_cycle__c)" will be counted as the unique field combination of your group by expression. The way you are building your query, probably all rows will be 1, since you have many fields to compose the "unique key".

Maybe the best way to do this is to write a "plain" query without any agregations and make the logic in your component.
SF_MonkeySF_Monkey
I tried that and I get the error "field 'Download_Cycle__c' can not be grouped in a query call"

I do not even how the logic will be implemented in the component since everything will in one row.
Alain CabonAlain Cabon
You just need an ordered SOQL query (there is no sum here) and the trick is to fill wrappers and lists (apex) according the block datatables used in the VFP or nested Json data for a lightning tree-grid for lightning / lwc (javascript).

https://developer.salesforce.com/docs/component-library/bundle/lightning:treeGrid/example
https://www.soliantconsulting.com/blog/multi-tiered-tables-in-visualforce/ 
select Download_Cycle__c, Name, Last_Download__c, Portal_Name__c, Password__c, Login__c, Location_Address__c, Location_Name__c from vendor_Invoice__c
order by Download_Cycle__c, Portal_Name__c, Name
This was selected as the best answer
SF_MonkeySF_Monkey
All of those fields are on the same object. Do I still need to use a wrapper class?
Alain CabonAlain Cabon
Right with just one object, you just need  breakBefore for <apex:column> like here :

Nested table using pageblock
https://salesforce.stackexchange.com/questions/49545/nested-table-using-pageblock

https://developer.salesforce.com/docs/atlas.en-us.pages.meta/pages/pages_compref_column.htm

I often used breakBefore and that works fine for a VFP.
 
SF_MonkeySF_Monkey
Thank for the help! I think that breakBefore will work great but I wanted to see if there are similar option in aura components. I am trying to avoid a VF page. Tree grid would not be an option for me since it doesnt support inline edit. Are there any other way that I can achieve this? Do you think that accordian will be a good fit for this?
Alain CabonAlain Cabon
Nested Json objects for a lightning tree-grid for Aura lightning  (javascript).

https://developer.salesforce.com/docs/component-library/bundle/lightning:treeGrid/example

The names in the first column will be either a Download_Cycle__c  or a Portal_Name__c (depending of the level of nested data).

Otherwise there are nested accordions probably.

https://developer.salesforce.com/docs/component-library/bundle/lightning:accordion/example#lightningcomponentdemo:exampleAccordionMultiple