0

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
2
Contributors
10
Replies
42
Views
3 Years
Discussion Span
Last Post by jacob21
0

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

0

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
0

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.

0
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
0

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

0

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
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.