Question

我在询问和分组方面遇到问题。

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

下面第5栏列出错误:

[DB2 for i5/OS]SQL0122 - ColumnNAME or expression in SlectT list not valid.

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

Answer 1

<编码> 按组别SETS分类的组别是组别/组别数据的一种POWERFUL工具。它请你将非分类数据与一个查询结果的合计数据结合起来。

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/... :

  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

结果是:

  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

友情链接