Results 1 to 2 of 2
  1. #1
    Join Date
    May 2011
    Posts
    28

    Unanswered: SQL using COUNT and CASE for related table - error is returned

    I have table T1:

    ID GROUPINFO STATUS
    1 GROUP1 NEW
    2 GROUP1 INPROG
    3 GROUP2 INPROG

    I have table T2 also

    ID STATUS
    1 NEW
    2 NEW
    2 VENDOR
    3 NEW
    3 VENDOR

    I want to count by group how many of those records have the status NEW, and how many of those records were in the status VENDOR (information from T2 table)

    This SQL works

    SELECT T1.GROUPINFO,
    count (case when T1.status='NEW' then 1 end) as New
    FROM T1
    GROUP BY T1.GROUPINFO

    However when I try to add how many of those records were in the VENDOR status I am getting an error:

    SELECT T1.GROUPINFO,
    count (case when T1.status='NEW' then 1 end) as New,
    count (case when T1.ID in (select T2.ID from T2 where T2.status='VENDOR') then 1 end) as Vendor
    FROM T1
    GROUP BY T1.GROUPINFO

    I am getting an error:

    Bad Plan; Unresolved QNC found

    I am not sure what I am doing wrong and how should I organize my SQL query so I could get this result for this example:

    GROUPINFO NEW VENDOR
    GROUP1 1 1
    GROUP2 0 1

    If ID exists multiple times in T2 it should only be counted 1 time because this is one record for which I want to check if it was in that status or not.

    Update:
    I also tried EXIST for that count case:

    SELECT T1.GROUPINFO,
    count (case when T1.status='NEW' then 1 end) as New,
    count (case when exists (select 1 from T2 where T2.ID=T1.ID and T2.status='VENDOR') then 1 end) as Vendor
    FROM T1
    GROUP BY T1.GROUPINFO

    but again having a SQL error: regarding the ID column

  2. #2
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    260
    Provided Answers: 39
    Try this:

    Code:
    select t1.groupinfo
    , count(case t1.status when 'NEW' then 1 end) as new
    , count(case t2.status when 'VENDOR' then 1 end) as vendor
    from t1
    join t2 on t1.id=t2.id
    group by t1.groupinfo
    Regards,
    Mark.

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
  •