DB2 SQL SUM 和 GROUPING
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10181835/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
DB2 SQL SUM and GROUPING
提问by macunte
I am having problems with querying and grouping.
我在查询和分组方面遇到问题。
I am needing the following output:
我需要以下输出：
officr, cbal, sname
ABC, 500.00, TOM JONES
ABC, 200.00, SUE JONES
ABC TOTAL 700.00
RAR, 100.10, JOE SMITH
RAR, 200.05, MILES SMITH
RAR TOTAL 300.15
SQL below produces the error:
下面的 SQL 产生错误：
[DB2 for i5/OS]SQL0122 - Column SNAME or expression in SELECT list not valid.
[DB2 for i5/OS]SQL0122 - SELECT 列表中的列 SNAME 或表达式无效。
SELECT
lnmast.officr, SUM(LNMAST.CBAL), lnmast.sname
FROM
LNMAST
WHERE LNMAST.RATCOD IN (6,7,8) AND STATUS NOT IN ('2','8')
group by lnmast.officr
回答by xQbert
GROUP BY GROUPING SETSis a POWERFUL tool for grouping/cubing data. It lets you combine non-aggregated data with aggregated data in one query result.
GROUP BY GROUPING SETS是一个强大的工具分组/cubing 数据。它允许您将非聚合数据与聚合数据合并到一个查询结果中。
SELECT lnmast.officr, SUM(LNMAST.CBAL), lnmast.sname
FROM LNMAST
WHERE LNMAST.RATCOD IN (6,7,8)
AND STATUS NOT IN ('2','8')
GROUP BY GROUPING SETS ((lnmast.officr, lnmast.sname),(lnmast.officr))
An example from IBM DOCS: www.ibm.com/support/knowledgecenter/en/...:
来自 IBM DOCS 的示例：www.ibm.com/support/knowledgecenter/en/...：
SELECT WEEK(SALES_DATE) AS WEEK,
DAYOFWEEK(SALES_DATE) AS DAY_WEEK,
SALES_PERSON, SUM(SALES) AS UNITS_SOLD
FROM SALES
WHERE WEEK(SALES_DATE) = 13
GROUP BY GROUPING SETS ( (WEEK(SALES_DATE), SALES_PERSON),
(DAYOFWEEK(SALES_DATE), SALES_PERSON))
ORDER BY WEEK, DAY_WEEK, SALES_PERSON
This results in:
这导致：
WEEK DAY_WEEK SALES_PERSON UNITS_SOLD
----------- ----------- --------------- -----------
13 - GOUNOT 32
13 - LEE 33
13 - LUCCHESSI 8
- 6 GOUNOT 11
- 6 LEE 12
- 6 LUCCHESSI 4
- 7 GOUNOT 21
- 7 LEE 21
- 7 LUCCHESSI 4
