CODEWITHSUNDEEP
sqloracle-database
Sirish Kumar Bethala
Sirish Kumar Bethala

Reputation: 9269

SQL Query with CASE and group by

I have a Feature Table where each feature is identified by its ID(DB column) and Bugs table where each feature has one to many relation ship with bugs table.

Feature Table has columns
id Description

Bugs Table has columns

ID Feature_ID Status

I will consider a bug as opened if its state is either 0 or 1 and as closed if Status is 2.

I am trying write a query which indicates whether a Feature can be considered as passed or failed based on it's Status.

 select F.ID
        CASE WHEN count(B.ID) > 0 THEN 'FAIL'
             ELSE 'PASS' 
         END as FEATURE_STATUS 
 from Feature F,
      Bugs B 
where B.Status in (0,1) 
group by F.ID;

My query always gives the Failed Features but not passed, how can modify my query to return both?

Upvotes: 0

Views: 338

Answers (2)

Bassam Mehanni
Bassam Mehanni

Reputation: 14944

SELECT F.ID, 
       CASE WHEN SUM(CASE WHEN B.ID IN (0, 1) THEN 1 ELSE 0 END) > 0 THEN 'Fail'
            ELSE 'Success' END AS FEATURE_STATUS 
 from Feature F
      JOIN Bugs B ON B.Feature_ID = F.ID    
group by F.ID

Upvotes: 2

Justin Cave
Justin Cave

Reputation: 231751

It sounds like you want something like

SELECT f.id,
       (CASE WHEN open_bugs = 0 
             THEN 'PASS'
             ELSE 'FAIL'
         END) feature_status,
       open_bugs,
       closed_bugs
  FROM (SELECT f.id,
               SUM( CASE WHEN b.status IN (0,1)
                         THEN 1
                         ELSE 0
                     END) open_bugs,
               SUM( CASE WHEN b.status = 2
                         THEN 1
                         ELSE 0
                     END) closed_bugs
          FROM feature f
               JOIN bugs b ON (f.id = b.feature_id)
         GROUP BY f.id)

Upvotes: 2

Related Questions