Count users from table 2 in request to table 1

I have table with positions

tbl_positions
   id position
    1 Driver
    2 Lobby
    3 S开发者_JS百科upport
    4 Constructor 

and in other table i have users

tbl_workers
  id   name   position status
   1   John   2        3
   2   Mike   3        2
   3   Kate   2        3
   4   Andy   1        0

i do request of positions

Without status I select everything with this query .

SELECT p.id, p.position, count(*) FROM  tbl_positions as p
inner join tbl_workers as w on w.position=p.id
group by p.id, p.position

But now i need output same query but also considers status, status 2=booked status, 3=placed. I need output like this in single query.

Position    booked placed
Driver        0       0
Lobby         0       2
Support       1       0
Constructor   0       0

I tried add WHERE tbl_workers.status IN (2) for booked and WHERE tbl_workers.status IN (3) for placed and it works in two queries, but no luck joining this into one query

Try this:

SELECT p.id, 
    p.position, 
    SUM(CASE w.Status WHEN 2 THEN 1 ELSE 0 END)  AS booked,
    SUM(CASE w.Status WHEN 3 THEN 1 ELSE 0 END)  AS placed
  FROM  tbl_positions AS p LEFT JOIN tbl_workers AS w 
      ON w.position=p.id
GROUP BY p.id, p.position

Although you can do this with a single SQL query, it may be more straightforward to handle this using PHP.

Use your current query and add status to the GROUP BY.

SELECT p.id, p.position, w.status, count(*) FROM  tbl_positions as p
inner join tbl_workers as w on w.position=p.id
group by p.id, p.position, w.status

You'll get a result set like this:

position    status  count
Driver        2       0
Lobby         2       0
Support       2       1
Constructor   2       0
Driver        3       0
Lobby         3       2
Support       3       0
Constructor   3       0

Use PHP to logically determine 2 = booked and 3 = placed.