SEO

I've been exploring some of the extended functionality with the GROUP
BY feature of SQL including ROLLUP, CUBE, etc.

I haven't been able to find exactly what I'm looking for to produce
the output I want.

Given T1 as:

ID NAME ST AMOUNT
-- ---- -- ------
1 Bob CA 10.00
2 Joe CA 25.00
3 Jim NY 50.00

I would like to see:

ID NAME ST AMOUNT NUM
-- ---- -- ------ ---
1 Bob CA 10.00
2 Joe CA 25.00
- - CA 35.00 2
3 Jim NY 50.00
- - NY 50.00 1
- - - 85.00 3

I am basically looking for subtotals and counts on only one column
(ST) and then a grand total.

When using ROLLUP(ID, NAME, ST), I also get subtotals when it breaks
on NAME and ID. The docs for GROUP by shows some examples using
GROUPING SETS, but I couldn't find the right combination.

I found an ugly solution by performing a UNION with something like:
MYDETAILQUERY

UNION ALL
-- subtotal by state
SELECT
cast(NULL as INT),
cast(NULL as char(10)),
ST,
SUM(AMOUNT)
FROM T1
GROUP BY ST

UNION ALL
-- grand total
SELECT
cast(NULL as INT),
cast(NULL as char(10)),
Cast(NULL as char(2)),
SUM(AMOUNT)
FROM T1

ORDER BY ST


This seems to produce the correct output, but my actual detail query
is the product of several other UNIONS (on a federated server with
multiple data sources) and is extremely cumbersome to code using SQL.

Does there exist an elegant solution to this using one of the GROUPing
functions?

Thanks,
Evan

lunch time....

but you can investigate the use of GROUPING().

(i'll come back later today ... maybe...)

PM


"Evan Smith" <esmith2112@hotmail.com> a écrit dans le message de news:
23658335.0306261338.3a382c0c@posting.google.com...
> I've been exploring some of the extended functionality with the GROUP
> BY feature of SQL including ROLLUP, CUBE, etc.
>
> I haven't been able to find exactly what I'm looking for to produce
> the output I want.
>
> Given T1 as:
>
> ID NAME ST AMOUNT
> -- ---- -- ------
> 1 Bob CA 10.00
> 2 Joe CA 25.00
> 3 Jim NY 50.00
>
> I would like to see:
>
> ID NAME ST AMOUNT NUM
> -- ---- -- ------ ---
> 1 Bob CA 10.00
> 2 Joe CA 25.00
> - - CA 35.00 2
> 3 Jim NY 50.00
> - - NY 50.00 1
> - - - 85.00 3
>
> I am basically looking for subtotals and counts on only one column
> (ST) and then a grand total.
>
> When using ROLLUP(ID, NAME, ST), I also get subtotals when it breaks
> on NAME and ID. The docs for GROUP by shows some examples using
> GROUPING SETS, but I couldn't find the right combination.
>
> I found an ugly solution by performing a UNION with something like:
> MYDETAILQUERY
>
> UNION ALL
> -- subtotal by state
> SELECT
> cast(NULL as INT),
> cast(NULL as char(10)),
> ST,
> SUM(AMOUNT)
> FROM T1
> GROUP BY ST
>
> UNION ALL
> -- grand total
> SELECT
> cast(NULL as INT),
> cast(NULL as char(10)),
> Cast(NULL as char(2)),
> SUM(AMOUNT)
> FROM T1
>
> ORDER BY ST
>
>
> This seems to produce the correct output, but my actual detail query
> is the product of several other UNIONS (on a federated server with
> multiple data sources) and is extremely cumbersome to code using SQL.
>
> Does there exist an elegant solution to this using one of the GROUPing
> functions?
>
> Thanks,
> Evan

i know its an ugly solution...

$ db2 "select * from t"

ID NAME STATE AMOUNT
----------- ---------- ----- -----------
1 Bob CA 10
2 Joe CA 25
3 Jim NY 50

3 record(s) selected.

$ db2 "select id, case when id > 0 then min(name) else null end, state,
sum(amount), case when id > 0 then null else count(*) end from t group by
rollup(state, id) order by state, id"

ID 2 STATE 4 5
----------- ---------- ----- ----------- -----------
1 Bob CA 10 -
2 Joe CA 25 -
- - CA 35 2
3 Jim NY 50 -
- - NY 50 1
- - - 85 3

6 record(s) selected.

$

cheers,
senthil

Evan Smith <esmith2112@hotmail.com> wrote in message
news:23658335.0306261338.3a382c0c@posting.google.c om...
> I've been exploring some of the extended functionality with the GROUP
> BY feature of SQL including ROLLUP, CUBE, etc.
>
> I haven't been able to find exactly what I'm looking for to produce
> the output I want.
>
> Given T1 as:
>
> ID NAME ST AMOUNT
> -- ---- -- ------
> 1 Bob CA 10.00
> 2 Joe CA 25.00
> 3 Jim NY 50.00
>
> I would like to see:
>
> ID NAME ST AMOUNT NUM
> -- ---- -- ------ ---
> 1 Bob CA 10.00
> 2 Joe CA 25.00
> - - CA 35.00 2
> 3 Jim NY 50.00
> - - NY 50.00 1
> - - - 85.00 3
>
> I am basically looking for subtotals and counts on only one column
> (ST) and then a grand total.
>
> When using ROLLUP(ID, NAME, ST), I also get subtotals when it breaks
> on NAME and ID. The docs for GROUP by shows some examples using
> GROUPING SETS, but I couldn't find the right combination.
>
> I found an ugly solution by performing a UNION with something like:
> MYDETAILQUERY
>
> UNION ALL
> -- subtotal by state
> SELECT
> cast(NULL as INT),
> cast(NULL as char(10)),
> ST,
> SUM(AMOUNT)
> FROM T1
> GROUP BY ST
>
> UNION ALL
> -- grand total
> SELECT
> cast(NULL as INT),
> cast(NULL as char(10)),
> Cast(NULL as char(2)),
> SUM(AMOUNT)
> FROM T1
>
> ORDER BY ST
>
>
> This seems to produce the correct output, but my actual detail query
> is the product of several other UNIONS (on a federated server with
> multiple data sources) and is extremely cumbersome to code using SQL.
>
> Does there exist an elegant solution to this using one of the GROUPing
> functions?
>
> Thanks,
> Evan

First one is a brute force method.
You generate everything then filter what you want.
The second example (functionnaly equivalent) is more on the minimalist
approach. (ask for what you want, no more.)

You can remove unneeded GROUPINGs in the code, you don't really need to
select them for output.

PM

db2 => SELECT *
db2 (cont.) => FROM
db2 (cont.) => ( SELECT ID, NAME, ST, SUM(AMOUNT), GROUPING(ID) AS GR_ID,
GROUPING(NAME) AS GR_NAME, GROUPING(ST)
db2 (cont.) => FROM DBOXA.TDL00
db2 (cont.) => GROUP BY CUBE(ID, NAME, ST)
db2 (cont.) => -- DETAIL
db2 (cont.) => HAVING (GROUPING(ID)=0 AND GROUPING(NAME)=0 AND
GROUPING(ST)=0)
db2 (cont.) => -- SUPER TOTAL
db2 (cont.) => OR (GROUPING(ID)=1 AND GROUPING(NAME)=1 AND
GROUPING(ST)=1)
db2 (cont.) => -- TOTAL BY STATE
db2 (cont.) => OR (GROUPING(ID)=1 AND GROUPING(NAME)=1 AND
GROUPING(ST)=0)
db2 (cont.) => ) REFA(RID, RNAME, RST, RSUM, GID, GNAME, GST)
db2 (cont.) => ORDER BY RST, RNAME, RID
db2 (cont.) =>
db2 (cont.) => ;

RID RNAME RST RSUM GID
GNAME GST
----------- -------------------- --- --------------------------------- -----
------ ----------- -----------
1 BOB CA 648432.9723
0 0 0
2 JOE CA 992310.8584
0 0 0
- - CA 1640743.8307
1 1 0
3 JIM NY 450114.9842
0 0 0
- - NY 450114.9842
1 1 0
- - - 2090858.8149
1 1 1

6 record(s) selected.

db2 => SELECT *
db2 (cont.) => FROM
db2 (cont.) => ( SELECT ID, NAME, ST, SUM(AMOUNT), GROUPING(ID) AS GR_ID,
GROUPING(NAME) AS GR_NAME, GROUPING(ST)
db2 (cont.) => FROM DBOXA.TDL00
db2 (cont.) => GROUP BY GROUPING SETS( (ID, NAME, ST), (ST), ())
db2 (cont.) => ) REFA(RID, RNAME, RST, RSUM, GID, GNAME, GST)
db2 (cont.) => ORDER BY RST, RNAME, RID
db2 (cont.) => ;

RID RNAME RST RSUM GID
GNAME GST
----------- -------------------- --- --------------------------------- -----
------ ----------- -----------
1 BOB CA 648432.9723
0 0 0
2 JOE CA 992310.8584
0 0 0
- - CA 1640743.8307
1 1 0
3 JIM NY 450114.9842
0 0 0
- - NY 450114.9842
1 1 0
- - - 2090858.8149
1 1 1

6 record(s) selected.

db2 => SELECT * FROM DBOXA.TDL00
db2 (cont.) => ;

ID NAME ST AMOUNT NUM
----------- -------------------- -- ------------ -----------
1 BOB CA 648432.9723 1116439830
2 JOE CA 992310.8584 305669232
3 JIM NY 450114.9842 1851645114

3 record(s) selected.

db2 =>

PM

esmith2112@hotmail.com (Evan Smith) wrote in message news:<23658335.0306261338.3a382c0c@posting.google. com>...
> I've been exploring some of the extended functionality with the GROUP
> BY feature of SQL including ROLLUP, CUBE, etc.
>
> I haven't been able to find exactly what I'm looking for to produce
> the output I want.
>
> Given T1 as:
>
> ID NAME ST AMOUNT
> -- ---- -- ------
> 1 Bob CA 10.00
> 2 Joe CA 25.00
> 3 Jim NY 50.00
>
> I would like to see:
>
> ID NAME ST AMOUNT NUM
> -- ---- -- ------ ---
> 1 Bob CA 10.00
> 2 Joe CA 25.00
> - - CA 35.00 2
> 3 Jim NY 50.00
> - - NY 50.00 1
> - - - 85.00 3
>
> I am basically looking for subtotals and counts on only one column
> (ST) and then a grand total.
>
> When using ROLLUP(ID, NAME, ST), I also get subtotals when it breaks
> on NAME and ID. The docs for GROUP by shows some examples using
> GROUPING SETS, but I couldn't find the right combination.
>

Here's one attempt using grouping sets

select id, name, state, sum(amount) as amount, count(1) as cnt
from t group by grouping sets ((id,name,state),(state),())

but then you have a problem with cnt -> 1 for non grouped values.
Can't come up with an elegant solution for that, but here is one shot
anyhow:

select id, name, state, sum(amount) as amount,
nullif(grouping(name)*count(1),0) as cnt from t group by grouping sets
((id,name,state),(state),())"

ID NAME STATE AMOUNT CNT
----------- ---- ----- ----------- -----------
- - - 85 3
- - CA 35 2
- - NY 50 1
1 BOB CA 10 -
2 JOE CA 25 -
3 JIM NY 50 -

6 record(s) selected.


HTH
/L

[...]

While neither of these two examples are the "elegant" solution I was
hoping to find, I appreciate the hints. I've managed to get both of
them to work (or at least seem to work). I'm thinking I can hybridize
them possibly to programmatically transform a detail query into such a
summary query.

Thanks again!

Evan

How about using CASE expression?

WITH t
(ID , NAME , ST , AMOUNT) AS (
SELECT SMALLINT(ID) , NAME , ST , AMOUNT
FROM (VALUES
(1 , 'Bob' , 'CA' , 10.00)
,(2 , 'Joe' , 'CA' , 25.00)
,(3 , 'Jim' , 'NY' , 50.00)
) AS X(ID , NAME , ST , AMOUNT)
)

SELECT id
, name
, st
, DEC(SUM(amount), 6,2 ) AS amount
, CASE
WHEN GROUPING(id) = 1 THEN CHAR(SMALLINT(COUNT(*)))
ELSE ''
END AS num
FROM t
GROUP BY GROUPING SETS ( (id, name, st) , (st) , () )
ORDER BY st, id
;
---------------------------------------------------

ID NAME ST AMOUNT NUM
------ ---- -- -------- ------
1 Bob CA 10.00
2 Joe CA 25.00
- - CA 35.00 2
3 Jim NY 50.00
- - NY 50.00 1
- - - 85.00 3

6 record(s) selected.

tonkuma@jp.ibm.com (Tokunaga T.) wrote in message news:<8156d9ae.0306300210.4942e26e@posting.google. com>...
> How about using CASE expression?
>

Nah, small is beautiful ;-)