选择具有不同where条件的相同列

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 = 1'条件），输出如下：

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:

我已经尝试使用UNION来组合单独的查询，如下所示：

 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?

我认为这可能会做我想要的，但它将两个总和放入一个名为'closed_total'的列中。有任何想法吗？

1 个解决方案

#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