Professional Web Applications Themes

GROUP BY Expertise in DB2 - IBM DB2

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

  1. #1

    Default GROUP BY Expertise in DB2

    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 bersome to code using SQL.

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

    Thanks,
    Evan
    Evan Smith Guest

  2. #2

    Default Re: GROUP BY Expertise in DB2

    lunch time....

    but you can investigate the use of GROUPING().

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

    PM


    "Evan Smith" <esmith2112hotmail.com> a écrit dans le message de news:
    [email]23658335.0306261338.3a382c0cposting.google.com[/email]...
    > 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 bersome to code using SQL.
    >
    > Does there exist an elegant solution to this using one of the GROUPing
    > functions?
    >
    > Thanks,
    > Evan

    PM \(pm3iinc-nospam\) Guest

  3. #3

    Default Re: GROUP BY Expertise in DB2

    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 <esmith2112hotmail.com> wrote in message
    news:23658335.0306261338.3a382c0cposting.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 bersome to code using SQL.
    >
    > Does there exist an elegant solution to this using one of the GROUPing
    > functions?
    >
    > Thanks,
    > Evan

    Senthil Ramanujam Guest

  4. #4

    Default Re: GROUP BY Expertise in DB2

    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




    PM \(pm3iinc-nospam\) Guest

  5. #5

    Default Re: GROUP BY Expertise in DB2

    [email]esmith2112hotmail.com[/email] (Evan Smith) wrote in message news:<23658335.0306261338.3a382c0cposting.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

    [...]
    Lennart Jonsson Guest

  6. #6

    Default Re: GROUP BY Expertise in DB2

    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
    Evan Smith Guest

  7. #7

    Default Re: GROUP BY Expertise in DB2

    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.
    Tokunaga T. Guest

  8. #8

    Default Re: GROUP BY Expertise in DB2

    [email]tonkumajp.ibm.com[/email] (Tokunaga T.) wrote in message news:<8156d9ae.0306300210.4942e26eposting.google. com>...
    > How about using CASE expression?
    >
    Nah, small is beautiful ;-)
    Lennart Jonsson Guest

Similar Threads

  1. "group by" - order of rows in group
    By aljosa.mohorovic@gmail.com in forum MySQL
    Replies: 1
    Last Post: September 1st, 08:50 AM
  2. May 29 Sydney Developers Group study group
    By 105 in forum Macromedia Flex General Discussion
    Replies: 0
    Last Post: May 25th, 02:56 PM
  3. cfgrid inside a <cfoutput query="myQuery" group="GROUP">
    By DavidGhous in forum Coldfusion Flash Integration
    Replies: 1
    Last Post: April 12th, 07:23 PM
  4. Looking for Freelancer w/director Expertise
    By videochick in forum Macromedia Director 3D
    Replies: 0
    Last Post: January 3rd, 07:33 PM
  5. Replies: 3
    Last Post: September 4th, 12:08 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139