按周汇总，即使是空行 [英] Summarise by week, even for empty rows

问题描述

我想按周汇总sales.quantity的总和，即使没有销售也要显示周数.

I want to summarise the sum of sales.quantity by week, and to show the week number even if there are no sales.

我已经设置了一个包含 1-54 的周表，以使用外部联接来强制所有周数通过，但它不起作用.它错过了没有销售的几周.

I have setup a weeks table with 1-54 in there to use an outer join to force all week numbers to come through, but it isn't working. It misses out weeks where there have been no sales.

我的查询是:

SELECT Weeks.WeekNum, SUM(sales.quantity) AS sales
FROM Weeks LEFT OUTER JOIN 
     sales ON Weeks.WeekNum = DATEPART(week, sales.transDate)
WHERE (sales.transDate BETWEEN @fromDate AND @toDate)
GROUP BY Weeks.WeekNum

任何帮助都会得到很大的帮助......这可能是我所做的一些愚蠢的事情！

ANY help would be greatly received... it's probably something stupid that I've done!

推荐答案

where 子句 WHERE (sales.transDate BETWEEN @fromDate AND @toDate) 将删除任何没有销售的周.您可能需要执行一个子查询来提取交易，然后将其加入您的周表.

The where clause WHERE (sales.transDate BETWEEN @fromDate AND @toDate) will remove any weeks without sales. You'll likely need to do a subquery to pull the transactions and then join that to your weeks table.

SELECT Weeks.WeekNum, SUM(sales.quantity) AS sales
FROM Weeks LEFT OUTER JOIN 
 (
    SELECT *
    FROM sales 
    WHERE (sales.transDate BETWEEN @fromDate AND @toDate)
 ) sales
    ON Weeks.WeekNum = DATEPART(week, sales.transDate)
GROUP BY Weeks.WeekNum

这篇关于按周汇总，即使是空行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！