CODEWITHSUNDEEP
mysqlselect
user3733831
user3733831

Reputation: 2926

Mysql select query with count

I need to create a select query with count from another table.

I have two tables like category and candidates. Every candidate belong to one category.

Here I want to select all categories from category table along with how many candidates available for each category.

like this: category_name(23)

This is how I tried it. but it doesn't work for me. 

SELECT c.category_id
         , c.name
         , COUNT(cn.job_category) AS cvs  
FROM job_category c  
LEFT JOIN candidates cn ON cn.job_category = c.category_id
ORDER BY c.name ASC

Upvotes: 0

Views: 52

Answers (1)

sgeddes
sgeddes

Reputation: 62841

You are missing the group by clause. 

SELECT c.category_id
         , c.name
         , COUNT(cn.job_category) AS cvs  
FROM job_category c  
    LEFT JOIN candidates cn ON cn.job_category = c.category_id
GROUP BY c.category_id
         , c.name
ORDER BY c.name ASC

Note: Without group by, mysql will just return an arbitrary category. It allows this behavior while most other database systems would result in an error when you don't include non-aggregated columns in the group by clause.

Upvotes: 2

Related Questions