SQL GROUP BY Question

The charity I work for provides a delivery service for groceries, food, wine and flowers in the city and neighbourhoods. The data used to be recorded with Excel but as I am about to leave, I have successfully been allowed to transfer the data to Access - making it easier for future developers to work with the data.

I would like to offer the functionality of displaying in a report,the number of orders for a particular shop per year. For example, let's say that we have 5 different Aldis in the DB. They are stored as Aldi Southern Quarter, Aldi West, Aldi East etc.

relations.PNG
I used the following SQL code to create a query:

PARAMETERS theyear SHORT; 

SELECT Month([deliverydate])                 AS theMonth, 
       Year([deliverydate])                  AS Year, 
       tblshops.shortname, 
       Count(tblshoppingtaxi.shoppingtaxiid) AS Orders 
FROM   tblshops 
       INNER JOIN tblshoppingtaxi 
               ON tblshops.[shopid] = tblshoppingtaxi.[shopid_fk] 
GROUP  BY Month([deliverydate]), 
          Year([deliverydate]), 
          tblshops.shortname 
HAVING ( ( ( Year([deliverydate]) ) = [theyear] ) 
         AND ( ( tblshops.shortname ) LIKE "Aldi*" ) );  

Open in new window


What happens is, that it groups all Aldis  by month and displays the number of orders for that particular month.

access.PNG
I do not want this - I just want to have a total for all Aldis per month.

Can this be done with SQL or should I resort to something else?
Massimo ScolaAsked:
Who is Participating?
 
_agx_Connect With a Mentor Commented:
It's probably because the query also groups by tblshops.shortname, not just the year/month.   To get a single total for each year/month, remove that column from the SELECT/GROUP. Also, for performance, it's better to do filtering in the WHERE clause instead of HAVING. Not tested, but something like this

SELECT Month([deliverydate])                 AS theMonth, 
       Year([deliverydate])                  AS Year, 
       Count(tblshoppingtaxi.shoppingtaxiid) AS Orders 
FROM   tblshops 
       INNER JOIN tblshoppingtaxi 
               ON tblshops.[shopid] = tblshoppingtaxi.[shopid_fk] 
WHERE  Year([deliverydate])  = [theyear]  
AND    tblshops.shortname  LIKE "Aldi*"

GROUP  BY Month([deliverydate]), 
          Year([deliverydate])

Open in new window

1
 
Olaf DoschkeConnect With a Mentor Software DeveloperCommented:
I second that, just to explain one more detail: HAVING is filtering data after grouping. That's too late to aggregate all Aldis into one group, you already have a shop groups and just filter all having Aldi in them. Therefore this has to be a WHERE clause, then you limit all data to Aldi shops and you don't group by shopname, you knwo you only want one result row per month.

A better solution could be to consolidate your shop list or introduce a ShopHoldings table with a holding company list, assign each shop to one of these and group by holdings. Then you can get monthly orders from all shop holdings and can throw out the WHERE clause, too.

Bye, Olaf.
0
 
Massimo ScolaAuthor Commented:
The query worked ... it was also the order it was set up.
I might add all shops that belongs to the same company/chain to a holding group.
Thanks a lot.
0
 
Olaf DoschkeSoftware DeveloperCommented:
Fine, notice while WHERE is evaluated before GROUP BY and that before HAVING, the execution order of any SQL is not really from left to right or top to bottom in general. Here you have a glimpse about execution order of a yet still simple one table query: https://sqlbolt.com/lesson/select_queries_order_of_execution

Bye, Olaf.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.