CODEWITHSUNDEEP
mysqlsqljoinunion
Makky
Makky

Reputation: 17463

MySQL union all columns

I have a mysql table for cases. It has product name for each product there can be multiple records with different statuses.

MySQL Fiddler : SQL Fiddler Link

SELECT 
product , count(*) as totalopen
FROM cases
where status='OPEN'
group by product
union all
SELECT 
product , count(*) as totalclosed
FROM cases
where status='CLOSED'
group by product

It is giving me result as Fiddler result

But I want result as enter image description here

What exactly I am missing in query. Any help will be appreciated.

Upvotes: 0

Views: 1467

Answers (3)

ahmed abdelqader
ahmed abdelqader

Reputation: 3560

Try the following code via using Inner Join:

Select a.product, totalopen , totalclosed
from (
SELECT 
product , count(*) as totalopen
FROM cases
where status='OPEN'
group by product ) a
inner join (
SELECT 
product ,count(*) as totalclosed
FROM cases
where status='CLOSED'
group by product ) b
on a.product = b.product.

Updated:-

For the products that has only one record, so its status has only CLOSED or OPEN use Full Outer Join instead of inner join as next:-

Select isnull(a.product,b.product) product, isnull(totalopen,0) totalopen , isnull(totalclosed,0) totalclosed
from (
SELECT 
product , count(*) as totalopen
FROM cases
where status='OPEN'
group by product ) a
full outer join (
SELECT 
product ,count(*) as totalclosed
FROM cases
where status='CLOSED'
group by product ) b
on a.product = b.product

Upvotes: 2

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

Use case expression and count the relevant ones. 

SELECT 
product ,
count(case when status = 'OPEN' then 1 end) as totalopen,
count(case when status = 'CLOSED' then 1 end) as totalclosed
FROM cases
group by product;

It uses the fact that

COUNT(1) = 1
COUNT(NULL) = 0

and

case when 1 = 1 then 1 end -- return 1
case when 1 = 2 then 1 end -- return null

Upvotes: 0

jarlh
jarlh

Reputation: 44766

Use case expressions to conditional aggregation:

SELECT product,
       count(case when status='OPEN' then 1 end) as totalopen,
       count(case when status='CLOSED' then 1 end) as totalclosed
FROM cases
where status in ('OPEN', 'CLOSED')
group by product

If you want to include products with only other status (e.g. pending), remove the WHERE clause.

Upvotes: 1

Related Questions