+ Start a Discussion
Aron SchorAron Schor 

SOQL help with a query showing QTY purchased per Product for a specific Account

Hi,

I am not sure if this is possible, but I would like to see a list of products a certain company has purchased, and the quantity purchased over a period of time.
ie
General Co Item A 25
General Co Item B 50

I have done some things on my own but can't quite get this.  I am still a bit new to SOQL.

This query works: (No rollup)
SELECT Description__c, Order_Quantity__c, Stock_Code__c, Order_Header__r.Customer_Name__C FROM Order_Detail__C WHERE Stock_Code__C = '6195'

This query works: (With Rollup, but no products) (it essentially shows how many Sales Orders we have in the system per Account.)
SELECT customer_Name__C, COUNT(id) total
    FROM Order_header__c
    GROUP BY ROLLUP(customer_Name__C) LIMIT 25

This query doesn't work (MALFORMED_QUERY: Field must be grouped or aggregated: Customer_Name__c)
SELECT Description__c, Order_Quantity__c, Stock_Code__c, Order_header__r.Order_Date__C, Order_Header__r.Customer_Name__C, COUNT(id) total
 FROM Order_Detail__C
 WHERE Order_Header__r.Customer_Name__C LIKE 'Conney' AND Order_header__r.Order_Date__C >= 2015-01-01
 GROUP BY ROLLUP(Stock_Code__C)

I have done some digging and found some possible terms to use but not sure which to use here or how.
count(id)
sum(pid= )
union all

Any help would be appreciated

Aron
John PipkinJohn Pipkin
try this:

SELECT Description__c, Order_Quantity__c, Stock_Code__c, Order_header__r.Order_Date__C, Order_Header__r.Customer_Name__C, COUNT(id) total
 FROM Order_Detail__C
 WHERE Order_Header__r.Customer_Name__C LIKE 'Conney' AND Order_header__r.Order_Date__C >= 2015-01-01
 GROUP BY ROLLUP(Stock_Code__C), Customer_Name__c
Aron SchorAron Schor
Thanks John, it says ERROR at Row:4:Column:31 unexpected token: , SELECT Description__c, Order_Quantity__c, Stock_Code__c, Order_header__r.Order_Date__C, Order_Header__r.Customer_Name__C, COUNT(id) total FROM Order_Detail__C WHERE Order_Header__r.Customer_Name__C LIKE 'Conney' AND Order_header__r.Order_Date__C >= 2015-01-01 GROUP BY ROLLUP(Stock_Code__C), Customer_Name__c
John PipkinJohn Pipkin
hmm.. I simplified the query a bit. try this:

SELECT Description__c, SUM(Order_Quantity__c),Stock_Code__c,Order_Header__r.Customer_Name__c
 FROM Order_Detail__C
 WHERE Order_Header__r.Customer_Name__C LIKE 'Conney' AND Order_header__r.Order_Date__C >= 2015-01-01
 GROUP BY Stock_Code__c, Order_Header__r.Customer_Name__c
Aron SchorAron Schor
I get this message
MALFORMED_QUERY: Field must be grouped or aggregated: Description__c

I removed Description__C to test but I get this.
Sorry, no records returned.

SELECT Stock_Code__c, SUM(Order_Quantity__c), Order_Header__r.Customer_Name__c
 FROM Order_Detail__C
 WHERE Order_Header__r.Customer_Name__C LIKE 'Conney' AND Order_header__r.Order_Date__C >= 2015-01-01
 GROUP BY Stock_Code__c, Order_Header__r.Customer_Name__c 

Account Conney does have sales.