Select sales from different stores and group by product

Question

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?

Answer 1

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