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 ScolaInternshipAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

_agx_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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Olaf DoschkeSoftware 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 ScolaInternshipAuthor 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.