CODEWITHSUNDEEP
mysqlsql
Brad
Brad

Reputation: 12262

need to return title and count of each department from mysql

tables:

departments: id, title

comments: id, year_code

department_comments: comment_id, department_id

I want to list every department title, with the count of how many comments it has.

something along the lines of 

select 
 d.title, count(c.id) as total_comments
from 
 departments d, comments c
left join
 department_comments dc on c.id = dc.comment_id 
where
 c.year_code = 1011

The above query does not work, but should give you an idea of what I am trying to accomplish.

What I want to display to the user is the department title and the total # of comments next to it, like below:

d.title (count(c.id))
---------------------
Maintenance (10)
Finance (5)
Security (13)

Upvotes: 0

Views: 89

Answers (1)

Joe Stefanelli
Joe Stefanelli

Reputation: 135809

You were close. Your JOIN syntax is a bit off and you're missing a GROUP BY.

SELECT d.title, COUNT(dc.comment_id) AS total_comments
    FROM departments d
        LEFT JOIN department_commments dc
            INNER JOIN comments c
                ON dc.commment_id = c.id
                    AND c.year_code = 1011
            ON d.id = dc.department_id
    GROUP BY d.title

Upvotes: 4

Related Questions