Professional Web Applications Themes

Getting multiple count of records that match a certain criteria in one sql query - IBM DB2

Hi, Let's say I have a table that looks like this: Record_id Name States 1 name1 A 2 name1 A 3 name1 A 4 name1 B 5 name1 B 6 name1 C 7 name1 C 8 name2 A 9 name2 A 10 name2 A 11 name2 A 12 name2 C 13 name2 C 14 name2 B I would like to get from a single query a result set like this: Name A B C ----- -- -- -- name1 3 2 2 name2 4 1 2 Basically, I am trying to get in one single sql query a count for ...

  1. #1

    Default Getting multiple count of records that match a certain criteria in one sql query

    Hi,

    Let's say I have a table that looks like this:

    Record_id Name States

    1 name1 A
    2 name1 A
    3 name1 A
    4 name1 B
    5 name1 B
    6 name1 C
    7 name1 C
    8 name2 A
    9 name2 A
    10 name2 A
    11 name2 A
    12 name2 C
    13 name2 C
    14 name2 B

    I would like to get from a single query a result set like this:


    Name A B C
    ----- -- -- --

    name1 3 2 2
    name2 4 1 2

    Basically, I am trying to get in one single sql query a count for the
    different states for every single name in the table. I came up with a
    sql query that does this but it is very ugly so I thought there had to
    be a better way of doing this. Any ideas? Thanks.
    Ricardo Guest

  2. #2

    Default Re: Getting multiple count of records that match a certain criteria in one sql query

    Ricardo <com> wrote:
     

    SELECT name,
    SUM(CASE WHEN states = 'A' THEN 1 ELSE 0 END) AS a,
    SUM(CASE WHEN states = 'B' THEN 1 ELSE 0 END) AS b,
    SUM(CASE WHEN states = 'C' THEN 1 ELSE 0 END) AS c
    FROM yourTable
    GROUP BY name

    or like this:

    SELECT t1.name, t1.a, t2.b, t3.c
    FROM ( SELECT name, COUNT(*)
    FROM yourTable
    WHERE states = 'A'
    GROUP BY name ) AS t1(name, a) JOIN
    ( SELECT name, COUNT(*)
    FROM yourTable
    WHERE states = 'B'
    GROUP BY name ) AS t2(name, b) ON
    ( t1.name = t2.name) JOIN
    ( SELECT name, COUNT(*)
    FROM yourTable
    WHERE states = 'C'
    GROUP BY name ) AS t3(name, c) ON
    ( t1.name = t3.name)


    --
    Knut Stolze
    Information Integration
    IBM Germany / University of Jena
    Knut Guest

  3. #3

    Default Re: Getting multiple count of records that match a certain criteria in one sql query

    Knut - Thanks for your reply. The first sql query in your reply was
    exactly what I neeeded. I came up with a sql query similar to the
    second one in your reply but I knew there had to be a better way.
    Ricardo Guest

  4. #4

    Default Re: Getting multiple count of records that match a certain criteria in one sql query

    Hi,

    you could issue like this

    SELECT name,
    SUM(CASE WHEN states = 'A' THEN 1 ELSE 0 END) AS a,
    SUM(CASE WHEN states = 'B' THEN 1 ELSE 0 END) AS b,
    SUM(CASE WHEN states = 'C' THEN 1 ELSE 0 END) AS c
    FROM tablename
    GROUP BY name

    hope this helps!
    Thanks,
    Dev
    N.V.Dev Guest

Similar Threads

  1. Query problem with multiple records output
    By DuLaus in forum Macromedia ColdFusion
    Replies: 5
    Last Post: March 24th, 05:50 PM
  2. Multiple search criteria
    By Adam in forum Microsoft Access
    Replies: 3
    Last Post: August 15th, 11:37 AM
  3. Only add one record in subform if criteria match
    By Charlie.Mitchell in forum Microsoft Access
    Replies: 0
    Last Post: July 30th, 05:18 PM
  4. Replies: 0
    Last Post: July 15th, 08:01 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