CODEWITHSUNDEEP
mysqlsqlpostgresql
sathishkumar
sathishkumar

Reputation: 347

Merging two select queries and add zeros to columns if does not have values

Hi Team am newbie to sql

I have two select queries like this 

select fv.value as primary_hod, count(*) as closed
from artifact a, item i, field_value fv
where i.id=a.id and i.folder_id = 'tracker1127' and fv.id = a.fv_0
  and a.close_date is not NULL  and i.date_created > i.date_created - INTERVAL '30 days'
group by fv.value;

This is returning

 primary_hod    | closed
------------------+--------
 Sivaramakrishnan |      1

And other Query 

select fv.value as primary_hod, count(*) as Open
from artifact a, item i, field_value fv
where i.id=a.id and i.folder_id = 'tracker1127' and fv.id=a.fv_0
  and a.close_date is NULL and i.date_created > i.date_created - INTERVAL '30 days'
group by fv.value;

This returns

   primary_hod    | open
------------------+------
 Sivaramakrishnan |    1
 Anand            |    2
 Manav            |    1
 Prasanna         |    1

And i want something like this 

  primary_hod    | open | Closed
------------------+------+
 Sivaramakrishnan |    1 | 1
 Anand            |    2 | 0
 Manav            |    1 | 0
 Prasanna         |    1 | 0

I tried with union and union all but it no luck please throw some light on this

Upvotes: 3

Views: 70

Answers (2)

jarlh
jarlh

Reputation: 44786

Use CASE expressions to do conditional counting:

select fv.value as primary_hod,
       count(case when a.close_date is NULL then 1 end) as open,
       count(case when a.close_date is not NULL then 1 end) as closed
from artifact a, item i, field_value fv
where i.id=a.id and i.folder_id = 'tracker1127' and fv.id = a.fv_0
  and i.date_created > i.date_created - INTERVAL '30 days'
group by fv.value;

Re-write with modern, explicit JOIN syntax:

select fv.value as primary_hod,
       count(case when a.close_date is NULL then 1 end) as open,
       count(case when a.close_date is not NULL then 1 end) as closed
from artifact a
  join item i on i.id = a.id
  join field_value fv on fv.id = a.fv_0
where i.folder_id = 'tracker1127' 
  and i.date_created > i.date_created - INTERVAL '30 days'
group by fv.value;

Upvotes: 3

Matt
Matt

Reputation: 15061

You can nest one in a sub query.

SELECT fv.value AS primary_hod, count(*) AS Open, (SELECT count(*) FROM artifact a
INNER JOIN item i ON i.id = a.id 
INNER JOIN field_value fv ON fv.id = a.fv_0 
WHERE i.folder_id = 'tracker1127' 
AND a.close_date IS NOT NULL  
AND i.date_created > i.date_created - INTERVAL '30 days' 
GROUP BY fv.value) AS Closed
FROM artifact a
INNER JOIN item i ON i.id = a.id 
INNER JOIN field_value fv ON fv.id = a.fv_0 
WHERE i.folder_id = 'tracker1127' 
AND a.close_date IS NULL  
AND i.date_created > i.date_created - INTERVAL '30 days' 
GROUP BY fv.value;

Upvotes: 1

Related Questions