Mysql select query with count

Question

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

sgeddes · Accepted Answer · 2016-03-22 03:12:14Z

2

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.

edited Mar 22, 2016 at 3:12

answered Mar 22, 2016 at 3:06

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql select query with count

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related