Count duplicate values in separate rows and order them

I have a tbl_press_release and tbl_pre开发者_如何学Pythonss_release_comment related by the column press_release_id. The tbl_comment has a row for every comment.

How do I count how many comments there are per press_release_id?

SELECT
    press_release_subject,
    (SELECT COUNT(press_release_id)
     FROM tbl_press_release_comment) as CommentCount
FROM
    tbl_press_release pr
ORDER BY
    CommentCount DESC

This returns every press release, but the CommentCount is the same for all of them.

---

You need to use the GROUP BY clause to correctly aggregate the comments with a JOIN statement to link the two tables.

SELECT pr.press_release_subject, COUNT(*) AS CommentCount
FROM tbl_press_release pr
    LEFT JOIN tbl_press_release_comment prc ON prc.press_release_id = pr.id
GROUP BY press_release_subject
ORDER BY CommentCount DESC

The join might not work as is because I don't know your column names.

Your current query, basically, asks for every press release subject, and the count of every single comment. Without a GROUP BY, your DBMS doesn't know how to separate the counts.

GROUP BY tells your DBMS that the aggregate functions must return one record per distinct value in the columns specified. For instance, the above query tells to count the number of comments for which the subject is press_release_subject. If you use aggregate functions (like COUNT, SUM, AVG, etc.), any regular column you want to return side-by-side must be specified in the GROUP BY clause.

---

SELECT
    pr.press_release_subject,
    COUNT(*) CommentCount 
FROM tbl_press_release pr
LEFT JOIN tbl_press_release_comment prc
ON pr.press_release_id = prc.press_release_id
GROUP BY pr.press_release_subject
ORDER BY  COUNT(*) DESC