CODEWITHSUNDEEP
phpmysql
Gurmeet
Gurmeet

Reputation: 3314

Bring all records along with count of records with different status in mysql

table:panel   

id PanelName email_id status
1   A        1        0
2   B        1        1 
3   C        1        0
4   D        1        1 
5   E        1        1 
-------------------------
6   A1       2        0 
7   B1       2        1 
8   C1       2        0
-------------------------
9   D1       3        1 
10  E1       3        1

I need all panels of user1 with status 1 (B,D,E) along with total no of panel of user1=5 and all count of panel of user1 with status=0 i.e 2 in a single query.

select p.*,Count(p1.id) as totalPanels
from panel p
INNER join panel p1 on (p.email_id=p1.email_id)
where p.email_id=1 and p.status =1
Group by p.id

This gives me all panel with status 1 and count of total panel. But how to bring count of panels with status 0 in same query?

Expected output will be:

id |PanelName| email_id |status| TotalPanel| Rejectedpanel

1   A          1         0       5           2
2   B          1         1       5           2
3   C          1         0       5           2 
4   D          1         1       5           2
5   E          1         1       5           2

Another soution for this is subquery as below but i dont have to use that

 select p.id,Count(p1.id) as totalPanels,
(select count(id) from panel p2 where p2.status=0 and p2.email_id=p.email_id ) as RejectePanel 
 from panel p
INNER join panel p1 on (p.email_id=p1.email_id)

where p.email_id=1 
Group by p.id

Please suggest, Thank you.

Upvotes: 2

Views: 508

Answers (2)

Kickstart
Kickstart

Reputation: 21513

Not sure why you can't use a sub query, although generally I would avoid a corelated sub query.

You could try something like this though

SELECT p.*, TotalPanel, AcceptedPanel, RejectedPanel
FROM panel p
INNER JOIN
(
    SELECT email_id, COUNT(*) AS TotalPanel, SUM(IF(status = 1, 1, 0)) AS AcceptedPanel, SUM(IF(status = 0, 1, 0)) AS RejectedPanel
    FROM panel 
    GROUP BY email_id
) Sub1
ON p.email_id = Sub1.email_id
WHERE p.email_id = 1

Upvotes: 0

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

Well, I'd totally go for 2 different queries but here is the mixed thing matching your expected output:

SELECT p1.*,
  (SELECT count(*) FROM panel p2
   WHERE p1.email_id = p2.email_id) TotalPanel,
  (SELECT sum(status = 0) FROM panel p2
   WHERE p1.email_id = p2.email_id) RejectedPanel
FROM panel p1
WHERE p1.email_id = 1

Fiddle here.

Upvotes: 2

Related Questions