Tricky query

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
 
A

Andrew

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.
 
S

Sonia

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.
 
S

Steve Schapel

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.
 
Top