Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2013
    Posts
    7

    Cool Unanswered: Summerization Query

    I have 3 Tables : T1 , T2 ,T3
    I need a query to get the count of T1 , and count of T2 and count of T3 , then total number as totally . but without using + operator .

    like
    -------------------------------
    Title | counts
    --------------------------------
    T1 | 20
    T2 | 23
    T3 | 10
    Total | 53
    -----------------------------------
    Last edited by michael0; 11-01-13 at 23:04.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    An example tested on DB2 9.7.5 for Windows.

    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT COALESCE(title, 'Total') AS title
         , SUM(counts)              AS counts
     FROM  (SELECT 'T1: Department'
                 , COUNT(*) FROM department
            UNION ALL
            SELECT 'T2: Employee'
                 , COUNT(*) FROM employee
            UNION ALL
            SELECT 'T3: Project'
                 , COUNT(*) FROM project
           ) s(title , counts)
     GROUP BY
           ROLLUP
           ( (title , counts) )
     ORDER BY
           GROUPING(s.title) /* a column name of the subquery s. */
         , title             /* an alias in SELECT list.         */
    ;
    ------------------------------------------------------------------------------
    
    TITLE          COUNTS     
    -------------- -----------
    T1: Department           9
    T2: Employee            42
    T3: Project             20
    Total                   71
    
      4 record(s) selected.

  3. #3
    Join Date
    Oct 2013
    Posts
    7

    THank you

    THANK YOU tonkuma so much ,, it is perfect !

    But what is the meaning of ROLLUP , and how can I know these advantages of DB2 ?

    Is there a good tutorial for these ?

    Regards

  4. #4
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by tonkuma View Post
    An example tested on DB2 9.7.5 for Windows.

    Code:
    [...]
     GROUP BY
           ROLLUP
           ( (title , counts) )
    [...]
    A minor thing, it is not necessary to include counts in the rollup.
    --
    Lennart

  5. #5
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by michael0 View Post
    THANK YOU tonkuma so much ,, it is perfect !

    But what is the meaning of ROLLUP , and how can I know these advantages of DB2 ?

    Is there a good tutorial for these ?

    Regards
    There's a small example at:

    IBM DB2 10.1 for Linux, UNIX, and Windows

    group by grouping sets ((a,b),(a),()) is equal to group by rollup(a,b)
    group by grouping sets ((a,b),(a),(b),()) is equal to group by cube(a,b)
    --
    Lennart

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Lennart,
    Thank you!

    It's true. Ammended code might be...
    Code:
    [...]
     GROUP BY
           ROLLUP
           ( title )
    [...]

Tags for this Thread

Posting Permissions

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