SQL Server: how to calculate different amounts in one query

Question

A friend suggested I post here, as I need a little 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 would like for the price to be sold in one selected amount as the total amount for the seller, but also the sale amount, where accessory = 1.

Grouping and combining are not what I'm stuck on, I can make a double amount.

In my head it would look like

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

But obviously this will 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