CODEWITHSUNDEEP
sqlpostgresql
Leo
Leo

Reputation: 1254

Make an entry in SQL query when no result is found

I have a query that counts values for an specific kind of values:

SELECT

 B.BG_USER_05 as Functionality,
 count(case when bg_status= 'New' then 1 end) as 'New',
 count(case when bg_status= 'Open' then 1 end) as 'Open',
 count(case when bg_status= 'Assigned' then 1 end) as 'Assigned',
 count(case when bg_status= 'Fixed' then 1 end) as 'Fixed',
 count(case when bg_status= 'Ready to Test' then 1 end) as 'Ready_to_Test',
 count(case when bg_status= 'Reopen' then 1 end) as 'Reopen',
 count(case when bg_status= 'Closed' then 1 end) as 'Closed',
 count(case when bg_status= 'Rejected' then 1 end) as 'Rejected'

FROM
    BUG B,
    RELEASES R

WHERE
    B.BG_DETECTED_IN_REL = R.REL_ID

GROUP BY
    B.BG_USER_05

The query works correctly and returns something like this:

UAT 5   13  2   3   0
SIT 14  82  59  18  8

The issue is that sometimes, there are no UAT elements in the table (which is completely normal), which will make the result something like this:

SIT 14  82  59  18  8

The problem is that I NEED the fist row to be UAT information, and I need the result to be somethin like this:

UAT 0   0   0   0   0
SIT 14  82  59  18  8

I have no idea how to approach this. Any idea?

Upvotes: 0

Views: 57

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

You can solve this using left outer join:

SELECT u.Functionality,
       count(case when bg_status= 'New' then 1 end) as "New",
       count(case when bg_status= 'Open' then 1 end) as "Open",
       count(case when bg_status= 'Assigned' then 1 end) as "Assigned",
       count(case when bg_status= 'Fixed' then 1 end) as "Fixed",
       count(case when bg_status= 'Ready to Test' then 1 end) as "Ready_to_Test",
       count(case when bg_status= 'Reopen' then 1 end) as "Reopen",
       count(case when bg_status= 'Closed' then 1 end) as "Closed",
       count(case when bg_status= 'Rejected' then 1 end) as "Rejected"
FROM (SELECT 'UAT' as functionality UNION ALL SELECT 'SIT') as u LEFT OUTER JOIN
     BUG B
     ON u.functionality = B.BG_USER_05 LEFT OUTER JOIN
     RELEASES R
     ON B.BG_DETECTED_IN_REL = R.REL_ID
GROUP BY u.functionality;

I also changed the query to use explicit join syntax (where the conditions are in the on clause). And, the column aliases use double quotes rather than single quotes. Single quotes should only be used for string and date constants; their use for identifiers often leads to confusion.

Upvotes: 1

Olesya Razuvayevskaya
Olesya Razuvayevskaya

Reputation: 1168

Maybe, something like this: 

SELECT Functionality, sum('New'), sum('Open'),sum('Assigned'),sum('Fixed'),sum('Ready_to_Test'),sum('Reopen'),sum('Closed'), sum('Rejected') 
from
(
SELECT

B.BG_USER_05 as Functionality,
count(case when bg_status= 'New' then 1 end) as 'New',
count(case when bg_status= 'Open' then 1 end) as 'Open',
count(case when bg_status= 'Assigned' then 1 end) as 'Assigned',
count(case when bg_status= 'Fixed' then 1 end) as 'Fixed',
count(case when bg_status= 'Ready to Test' then 1 end) as 'Ready_to_Test',
count(case when bg_status= 'Reopen' then 1 end) as 'Reopen',
count(case when bg_status= 'Closed' then 1 end) as 'Closed',
count(case when bg_status= 'Rejected' then 1 end) as 'Rejected'

FROM
   BUG B,
   RELEASES R

WHERE
   B.BG_DETECTED_IN_REL = R.REL_ID

GROUP BY
   B.BG_USER_05

UNION

SELECT 'UAT' AS Functionality,
0          AS 'New',
0          AS 'Open',
0          AS 'Assigned',
0          AS 'Fixed',
0          AS 'Ready_to_Test',
0          AS 'Reopen',
0          AS 'Closed',
0          AS 'Rejected'
)

group by Functionality

Upvotes: 0

Related Questions