Merge 2 queries

[jacob21]

Hi,
I am trying to merge below two queries in a single query such that

company_id,company_name,noOfOpenTickets,noOf PendingTickets

Need suggestions??

No Of open Tickets:

select c.company_id, (select company_name from company where id=company_id) as companyName,count(s.complaint_id)   as noOfOpenTickets from complaint3 c ,complaint3_status s where s.id =(select  max(d.id) from complaint3_status d where d.complaint_id =c.id  and   d.status_type in (1,3,4)) and s.status_value=0 and s.complaint_id =c.id   and c.created_at >=20131001 AND  c.created_at <=20131031  group by c.company_id

No of Pending Tickets:

select c.company_id, (select company_name from company where id=company_id) as companyName,count(s.complaint_id)   as noOfPendingTickets from complaint3 c ,complaint3_status s where s.id =(select  max(d.id) from complaint3_status d where d.complaint_id =c.id  and   d.status_type in (1,3,4)) and s.status_value=1 and s.complaint_id =c.id   and c.created_at >=20131001 AND  c.created_at <=20131031  group by c.company_id

[pritaeas]

Remove and s.status_value= and use a case with your count.

[jacob21]

Thanks..
Could you please give me an example of using case in mysql as i never used.

[pritaeas]

http://dev.mysql.com/doc/refman/5.7/en/case.html

COUNT(CASE WHEN s.status_value=0 THEN 1 ELSE 0 END CASE) AS Open,
COUNT(CASE WHEN s.status_value=1 THEN 1 ELSE 0 END CASE) AS Pending

This works too:

COUNT(IF(s.status_value=0, 1, 0)) AS Open,
COUNT(IF(s.status_value=1, 1, 0)) AS Pending

[jacob21]

Hey,

I am not getting right result.
any suggestions

select c.company_id, (select company_name from company where id=company_id) as companyName,COUNT(IF(s.status_value=0, 1, 0)) AS Open,COUNT(IF(s.status_value=1, 1, 0)) AS Pending
 from complaint3 c ,complaint3_status s where s.id =(select  max(d.id) from complaint3_status d where d.complaint_id =c.id  and   d.status_type in (1,3,4))  and s.complaint_id =c.id   and c.created_at >=20131001 AND  c.created_at <=20131031  group by c.company_id

[pritaeas]

I am not getting right result

Just dumping the query on me, without test data, without specifying what you expected as result and without the actual result is not enough for me to help you.

[jacob21]

company id   comapnName  noOfOpenTickets
 1              ab          10
and many records

if we changed s.status_value=1
company id   comapnName  noOfPendingTickets
 1              ab          10
and many records

if we changed s.status_value=2
company id   comapnName  noOfClosedTickets
 1              ab          10
and many records..

I am trying to merge data.

company id   comapnName  noOfClosedTickets  noOfPendingTickets noOfOpenTickets
 1              ab          10          10          10

 Need suggestions



    select c.company_id, (select company_name from company where id=company_id) as companyName,count(s.complaint_id)   as noOfOpenTickets from complaint3 c ,complaint3_status s where s.id =(select  max(d.id) from complaint3_status d where d.complaint_id =c.id  and   d.status_type in (2)) and s.status_value=0 and s.complaint_id =c.id   and c.created_at >=20131001 AND  c.created_at <=20131031  group by c.company_id

[pritaeas]

The above was obvious. You still haven't said what result you are getting now, what goes wrong with the query you tried.

[jacob21]

I am getting same result for open and pending

company_id companyName Open Pending

1 Go Air 7 7
2 Air India 7 7
3 Jet Airways 4 4

select c.company_id, (select company_name from company where id=company_id) as companyName,COUNT(IF(s.status_value=0, 1, 0)) AS Open,
COUNT(IF(s.status_value=1, 1, 0)) AS Pending
 from complaint3 c ,complaint3_status s where s.id =(select  max(d.id) from complaint3_status d where d.complaint_id =c.id  and   d.status_type in (2))  and s.complaint_id =c.id   and c.created_at >=20131001 AND  c.created_at <=20131031  group by c.company_id

[pritaeas]

Change COUNT to SUM and try again.

[jacob21]

Thanks for new learning