Tricky query

  • Thread starter Thread starter Sonia
  • Start date Start date
S

Sonia

Hi,

I'm reposting my question hoping to get some help since it was not answered
some time ago.

I need to track the orders placed by salesmen by month.



SELECT SUM(CASE MONTH(DATE_SELL) WHEN 1 THEN 1 ELSE 0 END) AS
January, ...
SUM(CASE MONTH(DATE_SELL) WHEN 12 THEN 1 ELSE 0
END) AS December
dbo.SALESMEN.SALESMAN_NAME AS SALESMAN,
dbo. SALESMEN.SALESMAN _ID
FROM dbo.ORDERS INNER JOIN
dbo.SALESMEN ON dbo.ORDERS.SALESMAN_ID = dbo.
SALESMEN.SALESMAN _ID
AND
dbo.ORDERS.SALESMAN_ID = dbo. SALESMEN.SALESMAN_ID
WHERE (YEAR(Y,dbo.ORDERS.DATE_SELL) = 2006 AND (dbo.
SALESMEN.SALESMAN_ID IN
(7,19, 24, 31, 35))
GROUP BY dbo. SALESMEN.SALESMAN_NAME, dbo. SALESMEN.SALESMAN_ID
ORDER BY dbo. SALESMEN.SALESMAN_NAME



RESULTS:


SALESMAN SALESMAN_ID ORDERS IN JAN

TONY 7 25
MIKE 19 25
DON 24 35
SAL 31 15
MARIA 35 12

However, Tony and Don belong to Sal, therefore I'd like to add Tony, Sal and
Don's orders as a whole to produce the following



EXPECTED RESULTS

SALESMAN JANUARY..
MARIA 12
MIKE 25
SAL 60



Can it be done and if so, how?

TIA

Sonia
 
Sonia,

There many ways to accomplish that...
I think you can go the "case" route or create a small table like:

SId, SalesGroup, SalesMan
7 0 DON
24 0 DON
31 0 DON
19 1 MIKE
35 2 MARIA

Then you can link SalesMan_ID to SID
and group by either SalesGroup or SalesMan.
 
Thanks Andrew but it doesn't solve my problem. If I use group then I cannot
display the individual names.
I think the solution is to use a sub query but just cannot make it work.
 
Sonia,

But your example illustrated that you don't want to display the
individual names, in that Don's and Tony's results are collapsed into
Sal's (except I think you got the maths wrong in the example, assuming I
understand what you meant). FWIW, I would do a similar concept to
Andrew's suggestion, in order to achieve the result you illustrated.
 
Back
Top