CODEWITHSUNDEEP
mysql
Sandeep
Sandeep

Reputation: 629

get calculation from two table with one query

I have two tables one is job and second one is applied_job, I want count result with one query, Please look into this 

job table                       job_applied table
-------------------------      -------------------------------------
j_id| job_title | salary       a_id   | applied_id | status | job_id
-------------------------      -------------------------------------
234 | PHP       | 50           2342   | 2          | 1      | 127 
235 | Ruby      | 102          2362   | 4          | 2      | 127 
127 | Python    | 150          2322   | 5          | 2      | 127 
1289| Java      | 180          2326   | 6          | 2      | 127
1274| .net      | 180          123    | 8          | 3     | 127

status 1 = selected
status 2 = rejected
status 3 = onhold

Now I want the result like this with one query

j_id  applied_count  rejected_count onhold_count selected
234   0              0              0            0
235   0              0              0            0 
127   5              3              1            1
1289  0              0              0            0
1274  0              0              0            0

Upvotes: 0

Views: 32

Answers (1)

Stefano Zanini
Stefano Zanini

Reputation: 5926

What you need is a combination of left join, group by and case:

select  t1.j_id,
        count(*) as applied_count
        sum(case when t2.status = 2 then 1 end) rejected_count,
        sum(case when t2.status = 3 then 1 end) onhold_count,
        sum(case when t2.status = 1 then 1 end) selected_count
from    job t1
left join
        job_applied t2
on      t1.j_id = t2.job_id
group by t1.j_id

All the fields in the resulting set are already available on job_applied, but you need that table to joined with job because you want all the available jobs, even if there's no job_applied row associated.

Upvotes: 2

Related Questions