CODEWITHSUNDEEP
mysqlsql
sambit.albus
sambit.albus

Reputation: 250

Want to select data from two tables, where i need to count a column and join

I have two data tables like table name block

|ID | Name |
|1  | B1   |
|2  | B2   |

Then i have another table named as block_worker

|block_id | Name | No Of Worker|
|1        | sam|
|1        | dave|
|2        |  Rob |

Now i want to write a query which show me data from block table and count the number of worker in block worker table according to the block id.

The result would be something like this

|ID | Name | Worker
|1 | B1 |   2    |
|2 | B2 |   1    |

Upvotes: 0

Views: 71

Answers (2)

juergen d
juergen d

Reputation: 204924

select b.id, b.name, count(w.name) as Workers
from block b
left join block_worker w on w.block_id = b.id
group by b.id, b.name

Upvotes: 1

Sanal K
Sanal K

Reputation: 733

Try Like this

    SELECT A.ID,A.Name,COUNT(B.block_id) AS Worker FROM block AS A JOIN block_worker AS B 
ON A.ID = B.block_id GROUP BY B.block_id

Upvotes: 1

Related Questions