SQL Query Showing Total Sales by Each Person and Each Product.

[drgoodvibe]

Hi There,

I'm trying to write a query that returns the total sames amount by each sales person for each product -- I'm having issues with the join sequence at this moment. Any help would be much appreciated!

Tables would look like this..

Products table
------------
ProductNum Name Price


SalesRep table
------------
PersonNum Name Region


Clients table
-----------
CustomerNum Company Region


Sales table
----------
OrderNUM CustomerNum PersonNum ProductNum TotalSales

[NeoPa]

You just need to focus on what you want as output first (the SELECT clause), then from there include all the tables involved in the FROM clause. At that point we consider how they connect together. This is fairly straightforward as logically they can only really connect one way. You'll see that in the design of the tables though - which FK fields connect to which PK fields.

Anyway, I expect you'd need something like :

SELECT   tSR.Name
       , tSR.Region
       , tP.Name
       , SUM(tS.TotalSales) AS TotalProdSales

Now we know only three of the tables are even required - [Products] (tP), [SalesRep] (tSR) and [Sales] (tS). Aliases will become evident in the FROM clause :

FROM    ([Sales] AS tS INNER JOIN
         [SalesRep] AS tSR
  ON     tS.PersonNum = tSR.PersonNum) INNER JOIN
         [Products] AS tP
  ON     tS.ProductNum = tP.ProductNum

That's all the hard part done. Now we need to add the GROUP BY clause as we want this per Sales Rep and per Product :

GROUP BY tSR.Name
       , tSR.Region
       , tP.Name

Welcome to Bytes!

[NeoPa]

A new question was posted in here which has been split across to CrossTab Problems. We don't encourage multiple questions in a single thread as it tends to cause all sorts of confusions and makes it less likely that things will work smoothly.