SQL Query to Group Shop Visits?

Question

I am trying to group the number of times a subscriber has visited each unique store but am getting an error:

select subscriberkey, storename, count (orderID) as shopvisits from salesorder 
   group by subscriberkey,storename

What is missing from/incorrect about this query?

Thanks

Answer 1

SOQL doesn't support 'as' keyword in aliasing. You can alias a table like

select o.subscriberykey, o.storname, o.orderid from salesorder o;

Also you should use Aggregate Functions in your case. Aggregate functions in SOQL, such as AVG(), SUM() and MAX(), allow you to roll up and summarize your data in a query.

AggregateResult[] groupedResults = [SELECT AVG(Amount)aver FROM Salesorder];
Object avgAmount = groupedResults[0].get('aver');

Note that any query that includes an aggregate function returns its results in an array of AggregateResult objects. AggregateResult is a read-only sObject and is only used for query results.

An example of Aggregate Function with a Group By clause. This is not the exact solution you are looking for as I don't know your data model. This is just for representation of an aggregate function which returns AggregateResult

AggregateResult result:[SELECT status__c status, Count(Id) Sum, 
                               subscriber__c subscriber 
                          FROM Salesorder 
                         WHERE status__c IN ('Fulfilled', 'Received') 
                      GROUP BY status__c, subscriber__C];