Selecting same column with different where conditions

Question

This query returns the sum of "closed" daily sales for a particular salesperson within a particular date range:

 SELECT SUM(price) as closed_total
 FROM   dbo.Sales
 WHERE  salesperson_ID = @salesperson_ID
        AND date_ordered BETWEEN @start_date AND @end_date
        AND closed = 1
 GROUP BY date_ordered

The output looks like:

daily_total
200
150
325
120
(etc)

I'd like to modify the query to return one column for closed sales, and one column for all sales (same query, but without the 'closed = 1' condition), with output like this:

closed_total | all_total
200          | 275
150          | 150
325          | 500
120          | 280
(etc)

I've tried using UNION to combine the separate queries, like this:

 SELECT SUM(price) as closed_total
 FROM   dbo.Sales
 WHERE  salesperson_ID = @salesperson_ID
        AND date_ordered BETWEEN @start_date AND @end_date
        AND closed = 1
 GROUP BY date_ordered
UNION ALL
 SELECT SUM(price) as all_total
 FROM   dbo.Sales
 WHERE  salesperson_ID = @salesperson_ID
        AND date_ordered BETWEEN @start_date AND @end_date
 GROUP BY date_ordered

I thought this might do what I was looking for, but it puts both sums into a single column named 'closed_total'. Any ideas?

Answer 1

You can try this

SELECT SUM(price) as total, SUM(CASE WHEN closed = 1 THEN price ELSE 0 END) as closed_total
 FROM   dbo.Sales
 WHERE  salesperson_ID = @salesperson_ID
        AND date_ordered BETWEEN @start_date AND @end_date
 GROUP BY date_ordered