Query Help
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Query Help

  1. #1
    Join Date
    Nov 2005
    Posts
    2

    Query Help

    Hello all

    I am having a problem and I hope you guys could help me with it. Essentially, I have to create a query. This essentially what my db tables look like.


    ----------------
    | Name | Status|
    ----------------
    |First | 1 |
    |First | 2 |
    |Second| 3 |
    |Second| 2 |
    |Third | 1 |
    ----------------


    From this table, I have to get the following table:

    --------------------------------------------
    |Name |Count Total|Count 1|Count 2|Count 3|
    --------------------------------------------
    |First | 2 | 1 | 1 | 0 |
    |Second| 2 | 0 | 1 | 1 |
    |Third | 1 | 1 | 0 | 0 |
    --------------------------------------------

    Here, "Count Total" is the total number of entries for each name, "Count 1" is the total number of entries with the Status 1 for each name, "Count 2" is for status 2, and so on.


    I have a feeling that this quiry can be done fairly easily, but I cannot envision it. Any help you guys could give would be great.

    Thank you in advance.

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Code:
    select
      name,
      count(*) as count_total,
      count(decode(status, 1, 1)) as count_1,
      count(decode(status, 2, 1)) as count_2,
      count(decode(status, 3, 1)) as count_3
    from my_table
    group by name;
    You can also use CASE expression instead of DECODE.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Nov 2005
    Posts
    2
    Thanks

    However, and this is my fault for not mentioning, but I am using SQL server, (yea, I know, wrong forum) and it does not seem to have the decode function. I tried replacing it with "case" but I don't think it is just that simple since I am getting a different error.

    Do you know this could work for SQL Server?

  4. #4
    Join Date
    Nov 2005
    Location
    Indianapolis
    Posts
    24

    You can use CASE

    You can use the case syntax in sql server. Try this...


    Code:
    select
      name,
      count(*) as count_total,
      count(case status when 1 then 1 end) as count_1,
      count(case status when 2 then 1 end) as count_2,
      count(case status when 3 then 1 end) as count_3
    from my_table
    group by name;

Posting Permissions

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



Click Here to Expand Forum to Full Width