Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2003
    Posts
    69

    Unanswered: multiple results in Single Query - How ??

    My requirement is like the bellow.

    I have a field called status which can only have "A" or "B" or "C" values.
    Now I need a write a sql query which will select the count of records for the status "A" and count of records for "B" and for "C" but it should be a single sql query.

    Anyone give me how to do this!!!.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    It is simple, use GROUP BY:

    Select status,count(*) form mytable group by status order by status

    HTH

    Andy

  3. #3
    Join Date
    Mar 2003
    Posts
    69
    But this will give a row wise result. But I need a column wise result!

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You want one row returned?

    Then it would be something like

    select (select count(*) from mytable where ststus = 'A') as acount,
    (select count(*) from mytable where status = 'B') as bcount,
    (select count(*) from mytable where status = 'C') as ccount
    from sysibm.sysdummy1

    HTH

    Andy

    Originally posted by antodomnic
    But this will give a row wise result. But I need a column wise result!

  5. #5
    Join Date
    Mar 2003
    Posts
    69
    But this is not working for me. It is saying that
    "ILLEGAL SYMBOL COUNT. SOME SYMBOLS...". I am using db2 6.1 running under OS/390!.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Maybe if you gave us your SQL code, I could see what you are doing wrong.

    Andy

    Originally posted by antodomnic
    But this is not working for me. It is saying that
    "ILLEGAL SYMBOL COUNT. SOME SYMBOLS...". I am using db2 6.1 running under OS/390!.

  7. #7
    Join Date
    Mar 2003
    Posts
    69
    ok bellow is the actual query I am trying ,

    SELECT (SELECT COUNT(*) FROM DB2PRD.HIRE_APLT A, DB2PRD.STORE B WHERE A.LOC_I = B.STORE_I AND A.LOC_TYPE_C = 'S' AND A.CUR_APLT_STAT_C IN ('HC')) as cur_aplt from from sysibm.sysdummy1




    But I tried another simple sql query like select (select count(*) from db2prd.hire_aplt) as count from sysibm.sysdummy1 which is also not working!.

  8. #8
    Join Date
    Apr 2003
    Location
    Florida
    Posts
    79
    Try this:

    select acnt, bcnt, ccnt
    from
    (select count(*) as acnt from mytable where status = 'A') as acount,
    (select count(*) as bcnt from mytable where status = 'B') as bcount,
    (select count(*) as ccnt from mytable where status = 'C') as ccount,
    ;

  9. #9
    Join Date
    Apr 2003
    Location
    Florida
    Posts
    79
    Ooops - take the last comma off.....

    select acnt, bcnt, ccnt
    from
    (select count(*) as acnt from mytable where status = 'A') as acount,
    (select count(*) as bcnt from mytable where status = 'B') as bcount,
    (select count(*) as ccnt from mytable where status = 'C') as ccount
    ;

  10. #10
    Join Date
    Mar 2003
    Posts
    69
    THAT WORKS PERFECTLY. THANK YOU VERY MUCH!!!!!!!!!!!!!!!!!!!!!!!

  11. #11
    Join Date
    Dec 2002
    Posts
    134
    I do not know if this is acceptable syntax on 6.1 Os390, but on UDB 7.1 aix you can do something like

    select
    count(case when status='A' then 1 end) a
    count(case when status='B' then 1 end) b,
    count(case when status='B' then 1 end) c,
    from mytable
    where status in ('A', 'B', 'C')

    It should be faster then 3 separate sub-queries

    regards,
    dmitri

  12. #12
    Join Date
    Apr 2003
    Location
    Trier, Germany
    Posts
    28
    You are right, this would be faster and more efficent, but does not work on DB2/390, even on V7.1.
    He has to use the solution mentioned before.

    On /390, the count-column function can only use the asterisk or a distinct column-name.

    Greetings Ingo

    Originally posted by chuzhoi
    I do not know if this is acceptable syntax on 6.1 Os390, but on UDB 7.1 aix you can do something like

    select
    count(case when status='A' then 1 end) a
    count(case when status='B' then 1 end) b,
    count(case when status='B' then 1 end) c,
    from mytable
    where status in ('A', 'B', 'C')

    It should be faster then 3 separate sub-queries

    regards,
    dmitri

Posting Permissions

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