如何在不对行数据进行分组的情况下将总计/小计添加到一组结果中?
我正在为业务报告构建 SQL 查询。我需要在报告中包含小计(按文件号分组)和总计。
我正在进入未知的 SQL 领域,所以这是第一次尝试。我所做的查询几乎有效。唯一的问题是条目正在分组——我需要它们在报告中分开。
这是我的示例数据:
FileNumber Date Cost Charge 3 Dec 22/09 5 10 3 Jan 13/10 6 15 3B Mar 28/10 1 3 3B Mar 28/10 5 10
当我运行此查询时,
SELECT
CASE
WHEN (GROUPING(FileNumber) = 1) THEN NULL
ELSE FileNumber
END AS FileNumber,
CASE
WHEN (GROUPING(Date) = 1) THEN NULL
ELSE Date
END AS Date,
SUM(Cost) AS Cost,
SUM(Charge) AS Charge
FROM SubtotalTesting
GROUP BY FileNumber, Date WITH ROLLUP
ORDER BY
(CASE WHEN FileNumber IS NULL THEN 1 ELSE 0 END), -- Put NULLs after data
FileNumber,
(CASE WHEN Date IS NULL THEN 1 ELSE 0 END), -- Put NULLs after data
Date
我得到以下信息:
FileNumber Date Cost Charge 3 Dec 22/09 5 10 3 Jan 13/10 6 15 3 NULL 11 25 3B Mar 28/10 6 13 <-- 3B NULL 6 13 NULL NULL 17 38
我想要是:
FileNumber Date Cost Charge 3 Dec 22/09 5 10 3 Jan 13/10 6 15 3 NULL 11 25 3B Mar 28/10 1 3 <-- 3B Mar 28/10 5 10 <-- 3B NULL 6 13 NULL NULL 17 38
我可以清楚地看到为什么对条目进行分组,但我没有考虑如何将它们分开,同时仍然返回小计和总计。
当谈到执行这样的高级 SQL 查询时,我有点陌生,因此,如果我使用 WITH ROLLUP
采取了错误的方法来解决问题,请建议一些首选替代方案 - 你不不必为我编写整个查询,我只需要一些指导。谢谢!
I'm constructing a SQL query for a business report. I need to have both subtotals (grouped by file number) and grand totals on the report.
I'm entering unknown SQL territory, so this is a bit of a first attempt. The query I made is almost working. The only problem is that the entries are being grouped -- I need them separated in the report.
Here is my sample data:
FileNumber Date Cost Charge 3 Dec 22/09 5 10 3 Jan 13/10 6 15 3B Mar 28/10 1 3 3B Mar 28/10 5 10
When I run this query
SELECT
CASE
WHEN (GROUPING(FileNumber) = 1) THEN NULL
ELSE FileNumber
END AS FileNumber,
CASE
WHEN (GROUPING(Date) = 1) THEN NULL
ELSE Date
END AS Date,
SUM(Cost) AS Cost,
SUM(Charge) AS Charge
FROM SubtotalTesting
GROUP BY FileNumber, Date WITH ROLLUP
ORDER BY
(CASE WHEN FileNumber IS NULL THEN 1 ELSE 0 END), -- Put NULLs after data
FileNumber,
(CASE WHEN Date IS NULL THEN 1 ELSE 0 END), -- Put NULLs after data
Date
I get the following:
FileNumber Date Cost Charge 3 Dec 22/09 5 10 3 Jan 13/10 6 15 3 NULL 11 25 3B Mar 28/10 6 13 <-- 3B NULL 6 13 NULL NULL 17 38
What I want is:
FileNumber Date Cost Charge 3 Dec 22/09 5 10 3 Jan 13/10 6 15 3 NULL 11 25 3B Mar 28/10 1 3 <-- 3B Mar 28/10 5 10 <-- 3B NULL 6 13 NULL NULL 17 38
I can clearly see why the entries are being grouped, but I have no idea how to separate them while still returning the subtotals and grand total.
I'm a bit green when it comes to doing advanced SQL queries like this, so if I'm taking the wrong approach to the problem by using WITH ROLLUP
, please suggest some preferred alternatives -- you don't have to write the whole query for me, I just need some direction. Thanks!
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)