SQL Server: How to calculate different sums in a single query

Question

A friend has suggested I post here as I'm in need of a bit of help!

DB Layout:

**salestable**
salesorder [primary, unique] (sales order number)
salesman (salesperson id)

**salesline**
salesorder [many sales line to single salestable relationship]
saleprice (line amount)
isaccessory (0 or 1)

I'd like to, in a single select, sum sales price as a total per salesman, but also sum sales price where is accessory = 1.

The grouping and joins aren't what I'm stuck on, it's whether I can do the dual sum.

In my head it would look something like

...salesman,sum(amount) as totalsales,sum(amount where accessory=1) as accessorysales...

But obviously that would never work!

Thanks in advance! (PS, I'm very new here, be gentle...)

Answer 1

SELECT  salesman, SUM(amount), SUM(CASE WHEN accessory = 1 THEN amount ELSE 0 END)
FROM    salestable t
JOIN    salesorder o
ON      o.salesorder = t.salesorder
GROUP BY
        salesman