macunte macunte - 2 months ago 13
SQL Question

DB2 SQL SUM and GROUPING

Having problems 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 error of:


[DB2 for i5/OS]SQL0122 - Column SNAME or expression in SELECT 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

publib.boulder.ibm.com

Group by grouping sets POWERFUL analytic for grouping/cubing data. lets you combine non-aggregated data with aggregated data in one query result.

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))

and an example from link above:

  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
Comments