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
$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.
Answer by ThomasRushton ·
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.