question

wpflum avatar image
wpflum asked

Is it possible to group on multiple columns by an OR statement?

I have a table that has a salesman number in one column, Sold By, and a 'Taken For' salesman number in another for a sale. I want to group and sum sales by the salesman's number in either the Sold By column or the Taken For column.

An example is:

Sale Sold By Taken For

$10 A

$10 B

$20 B A

Results in Salesman A getting a total of $30 while salesman B only gets the sales from the one he did on his own.

I'm new to this kind of SQL, I mostly used simple statements to pull data, this is the first time I'm trying to do the heavy lifting in the SQL server instead of having the end program, Crystal Reports, Access, etc doing it.

grouping
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
ThomasRushton avatar image
ThomasRushton answered

Yes, it is possible. You need to combine your aggregation (SUM) with a CASE expression, eg:

SELECT SoldBy, SUM(CASE WHEN TakenFor IS NULL THEN Sale ELSE 0 END)...

...but that doesn't take into account the possibility of a salesman only appearing in the "TakenFor" field. Hence, in your particular case, you might find it easier to do this calculation when joining to a separate table of all Salesmen IDs:

SELECT salesman.ID, SUM(CASE WHEN  TakenFor=Salesman.ID OR TakenFor IS NULL AND SoldBy=Salesman.ID THEN Sale ELSE 0 END)
FROM salesman, sales
GROUP BY salesman.ID

Or you could generate that list dynamically:

WITH salesmen AS (SELECT DISTINCT TakenFor AS SalesManID FROM Sales UNION SELECT DISTINCT SoldBy FROM Sales)
SELECT ...as above

Hopefully one of these gets you going in the right direction.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

wpflum avatar image
wpflum answered

Thanks, I'll give it a try when I can get back to working on Crystal Reports. Never fails when I start something new like this something always interrupts the learning curve.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.