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?
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With