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
RelaxItsJustCodeRelaxItsJustCode 

Need some SOQL help with aggregate, will give kudos

 

 

First problem, when I enter the "Having" statement in soql the Force.com Explorer freezes up.

 

Second problem, When I run it without the "Having" and with a formula field other than ProductId__c this query runs successfully but not as desired.

 

When I use other formula fields to summorize by like Product_Name__c which is another formula field this query runs just fine but again without the having and the product id it doesn't work as desired. 

 

This is the formula fields definition: PricebookEntry.Product2Id - on the OpportunityLineItem object.

 

Below is the query with a id hard coded for testing purposes in the Force.com Explorer.

 

select 
    ProductId__c
    ,sum(Quantity) Quantity 
From 
    OpportunityLineItem 
Where 
    (Classification__c = 'Software' 
    or Classification__c = 'Professional Services' 
    or Classification__c = 'Bundle (SW w/ HW)') 
    and Case_Id__c = '5007000000LcHVP'
Group By 
    ProductId__c
Having 
    sum(Quantity) > 1

 I will give kudos to anyone that has an idea as to where I've gone wrong.

 

Thank you,

Steve Laycock

 

Best Answer chosen by Admin (Salesforce Developers) 
Team WorksTeam Works

Follow this usecase:

To know more about AggregateResult : http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_query_aggregateresult.htm

(Mark this as solution and hit kudos if it helps)

for(AggregateResult ar : [SELECT Count(Id) c, WhatId w, Owner.Profile.Name
FROM Task
WHERE whatId IN :accIds
AND Subject Like 'Call%'
AND CreatedDate = This_Month
AND Owner.Profile.Name = 'System Administrator'
GROUP BY WhatId, Owner.Profile.Name])
{
Account acc = new Account();

string str = string.valueOf(ar.get('w'));

acc = accMap.get(str); // Edited //

 

All Answers

Dhaval PanchalDhaval Panchal
I tried your query in Workbench and it works for me.

use workbench : https://workbench.developerforce.com/
RelaxItsJustCodeRelaxItsJustCode

That’s awesome I didn’t know about the work bench!

 

One issue though, When I group by

 

 

select

    Product_Name__c

    ,max(ProductId__c) ProductId

    ,sum(Quantity) QuantitySum

From

    OpportunityLineItem

Where

    (Classification__c = 'Software'

    or Classification__c = 'Professional Services'

    or Classification__c = 'Bundle (SW w/ HW)')

    and Case_Id__c = '5007000000LcHVP'

Group By

    Product_Name__c

Having

    sum(Quantity) > 0

 

The third column doesn’t render a name in the workbench result set, it just says "Unknown_Field__1".

 

Any ideas?

 

Please let me know what you think can be done to address the naming of the third column please?

 

Thanks soo much, I really appreciate the introduction to the workbench!

 

Steve
Dhaval PanchalDhaval Panchal
Hi MeanGeeks

It always shows name like "Unknown_Field__1" for aggregate field.
if there are two aggregate fields then it will show "Unknown_Field__1" and "Unknown_Field__2".
RelaxItsJustCodeRelaxItsJustCode

Ok one last question then you will get credit for the solution as well.....

 

How do I referance the field in apex, my exp is that if the field wasn't given a name in the query then it would appear like expr0, expr1 etc.....  

 

That said, can you easily tell me how I access the "Unknown_Field__1" like columns to be used in apex code later?

 

Thanks again,

Steve 
Team WorksTeam Works

Follow this usecase:

To know more about AggregateResult : http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_query_aggregateresult.htm

(Mark this as solution and hit kudos if it helps)

for(AggregateResult ar : [SELECT Count(Id) c, WhatId w, Owner.Profile.Name
FROM Task
WHERE whatId IN :accIds
AND Subject Like 'Call%'
AND CreatedDate = This_Month
AND Owner.Profile.Name = 'System Administrator'
GROUP BY WhatId, Owner.Profile.Name])
{
Account acc = new Account();

string str = string.valueOf(ar.get('w'));

acc = accMap.get(str); // Edited //

 

This was selected as the best answer
Dhaval PanchalDhaval Panchal
My answer is same with "namo", hope you got your answer.