CODEWITHSUNDEEP
mysqlsqlsql-serverselectcount
Lekhulal Mathalipara
Lekhulal Mathalipara

Reputation: 57

Count same id column with different condition in same select statement

I need to combine both select statements into one select

SELECT count(tbl_employer_post_details.employer_id) pending
  FROM tbl_employer_post_details, 
       tbl_employer_registration
 WHERE job_status=0 
   AND tbl_employer_registration.employer_id = 
                                   tbl_employer_post_details.employer_id
 LIMIT start,max;

And the second query, with the only difference being the WHERE job_status=1:

SELECT count(tbl_employer_post_details.employer_id) approved
  FROM tbl_employer_post_details, 
       tbl_employer_registration
 WHERE job_status=1 
   AND tbl_employer_registration.employer_id = 
                         tbl_employer_post_details.employer_id
  LIMIT start,max;

Upvotes: 2

Views: 1047

Answers (4)

Mihai
Mihai

Reputation: 26784

SELECT count(tbl_employer_post_details.employer_id) PostDetails
FROM tbl_employer_post_details, tbl_employer_registration
WHERE job_status IN(0,1) AND  
tbl_employer_registration.employer_id=tbl_employer_post_details.employer_id LIMIT start,max;

SELECT SUM(job_status = 0) pending,
       SUM(job_status = 1) approved
FROM tbl_employer_post_details, tbl_employer_registration
WHERE job_status IN(0,1) AND  
tbl_employer_registration.employer_id=tbl_employer_post_details.employer_id LIMIT start,max;

Upvotes: 1

Luuk
Luuk

Reputation: 145

Try it like this.

  SELECT SUM(case when job_status = 0 then 1 else 0 end) pending,
         SUM(case when job_status = 1 then 1 else 0 end) approved
  FROM tbl_employer_post_details d JOIN tbl_employer_registration r
    ON r.employer_id = d.employer_id
 WHERE job_status IN (0, 1)

Upvotes: 0

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

SELECT SUM(job_status = 0) pending, SUM(job_status = 1) approved 
FROM tbl_employer_post_details epd 
INNER JOIN tbl_employer_registration er ON epd.employer_id = er.employer_id
WHERE job_status IN (0, 1);

Upvotes: 0

peterm
peterm

Reputation: 92795

Try it this way

SELECT SUM(job_status = 0) pending,
       SUM(job_status = 1) approved
  FROM tbl_employer_post_details d JOIN tbl_employer_registration r
    ON r.employer_id = d.employer_id
 WHERE job_status IN (0, 1)

Upvotes: 2

Related Questions