Results 1 to 5 of 5

Thread: Select Query

  1. #1
    Join Date
    Mar 2003
    Location
    Singapore
    Posts
    200

    Unanswered: Select Query

    I have a table with 2 fields,user and status.the data is like:

    amen A
    alen A
    Alex R
    alex1 P
    Alex2 S
    alex3 S

    I wana get a querry so it returns the values as:

    A R P S Total
    2 1 1 2 6

    I want to get the Sum of each status and Sum total of all status together

    mickykt

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    Try the following SQL.

    SELECT
    STATUS, COUNT(*) AS COUNT
    FROM table
    GROUP BY STATUS
    UNION
    SELECT
    'TOTAL' AS STATUS, COUNT(*) AS COUNT
    FROM table

    Hope this helps,
    Grofaty

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Grofty's suggestion displays the results as a table with n number of rows ...

    But if you want the result set in a single row, multiple column format, then (this is based on grofty's query)

    with temp*status,count) as (
    SELECT
    STATUS, COUNT(*) AS COUNT
    FROM table
    GROUP BY STATUS
    UNION ALL
    SELECT
    'TOTAL' AS STATUS, COUNT(*) AS COUNT
    FROM table
    )

    select
    case when status='A' then count end,
    case when status='B' then count end
    from temp

    As you can, this can be done only if there are finite number of status codes ...

    There should be some better solution ... mmm

    Cheers

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Jul 2003
    Posts
    34
    I guess OLAP features can be used to do this.

    db2 "select colb, count(*) from abc group by grouping sets (colb,())"

  5. #5
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Originally posted by asram
    I guess OLAP features can be used to do this.

    db2 "select colb, count(*) from abc group by grouping sets (colb,())"
    Which can also be written as...

    select colb, count(*) from abc group by colb with rollup

    'rollup' and 'cube' are convenience methods for 2 of the most common grouping sets requirements.

    I don't know about you but I for one find grouping sets pretty damn confusing at times!

Posting Permissions

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