此查询返回特定日期范围内特定销售人员的“已结算”每日销售额总和:
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
输出如下:
daily_total
200
150
325
120
(etc)
我想修改查询以返回一列用于已关闭的销售,一列用于所有销售(相同的查询,但没有'closed = 1'条件),输出如下:
closed_total | all_total
200 | 275
150 | 150
325 | 500
120 | 280
(etc)
我已经尝试使用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
我认为这可能会做我想要的,但它将两个总和放入一个名为'closed_total'的列中 . 有任何想法吗?
1 回答
你可以试试这个