0

I have three tables (SalesTable, StoresTable and ProductsTable) and the structure is the following:

--StoresTable
StoreId Name
1       Store 1
2       Store 2

--ProductsTable
Id    Description
1     Product 1
2     Product 1

--SalesTable
Store    Product    Qty
Store 1  Product 1  20
Store 1  Product 1  30
Store 1  Product 2  10
Store 2  Product 1  30
Store 2  Product 2  10
Store 1  Product 2  5

What I want is to display a report to show the sum of qty sales from each product and group by store. Something like this:

--Expected Result according above table data
Store     Product     Sales
Store 1   Product 1   50
Store 1   Product 2   15
Store 2   Product 1   30
Store 2   Product 2   10

At the moment I have the following query:

SELECT ST.Name, sum(S.Qty) as Sales, P.Description
FROM SalesTable S
INNER JOIN StoresTable ST ON S.Store = ST.Name
INNER JOIN ProductsTable P ON S.Product= ST.Description
group by ST.Name, Sales, P.Description

This query returns something like this (according with my real data info):

Store    Sales  PRODUCT
STORE 1  12     PRODUCT 1
STORE 1  3      PRODUCT 1
STORE 1  24     PRODUCT 2
STORE 1  48     PRODUCT 2
STORE 1  132    PRODUCT 2

As you can see the problem is that is duplicating products, I think that maybe is a problem with my joins. What I'm doing wrong?

  • This shouldn't compile: group by ST.Name, Sales, P.Description because Sales is not a column you can group by. Are you sure that's the query you have now? Or does one of the tables have a column called Sales? – Aaron Bertrand Oct 11 '18 at 23:12
2

Take sales out of the group by

SELECT ST.Name, sum(S.Qty) as Sales, P.Description
FROM SalesTable S
INNER JOIN StoresTable ST ON S.Store = ST.Name
INNER JOIN ProductsTable P ON S.Product= ST.Description
group by ST.Name, P.Description

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.