CODEWITHSUNDEEP
mysql
Karoly S
Karoly S

Reputation: 3258

MySQL query to count occurence of specific column value relating to id column

I'm wondering how to do a MySQL query that counts how many times a certain column value appears. For example I have a table with various columns, i'm interested in the id and prop_id columns. I want to count how many times a prop_id is 2, in relation to each id. 

id - prop_id
1  - a
1  - b
2  - c
3  - b
3  - b
3  - c

The results of my query would be: 

id - count(prop_id)
1 - 1
2 - 0
3 - 2

I'm having trouble translating this into a query despite it probably being fairly simple. I'm sure my terminology is off too, my knowledge of MySql is fairly basic, for now.

Upvotes: 0

Views: 19

Answers (1)

Shadow
Shadow

Reputation: 34231

Since you want to list all the id values, you need to complicate your query by retrieving all ids in a subquery and left join that on your table:

select t.id, count(t2.id) as props_count
from (select distinct id from table) t
left join table t2 on t.id=t2.id and t2.prop_id='b'
group by t.id

Upvotes: 2

Related Questions