Get total amount of rows from different table with matching ID

Question

For my first table i have questions like this:

qid  | question | date
 1      blah      22-05-2009

and then i have the table comments

cid | qid
 1     1
 2     1 
 3     1

so then in my questions table i could have an added column which had total_comments which would be three

Ive tryed using this code

SELECT
questions.qid,
questions.question,
questions.date,
sum(comments.qid) AS total_money
FROM
questions
INNER JOIN comments ON comments.qid = questions.qid
ORDER BY questions.date
LIMIT 1

but it errors and only grabs the first row when there is a row with a greater date? Thanks in advance

Answer 1

  SELECT COUNT(qid), qid
    FROM comments
    GROUP BY qid

Will show you the number of comments per qid.
If you want a specific qid count, it would be:

SELECT COUNT(qid)
FROM comments
WHERE qid = 1

Answer 2

You need to have "GROUP BY questions.qid, questions.question, questions.date" in your statement before the "ORDER BY".

Answer 3

If i understand correctly, I think you want:

SELECT questions.qid, question, date, SUM(comments.qid) 
FROM Questions 
LEFT OUTER JOIN Comments ON Questions.qid = Comments.qid
GROUP BY Questions.qid, Question, Date
ORDER BY Questions.Date

Answer 4

Try:

;WITH comment_summary AS (
    SELECT comments.qid
        ,COUNT(*) AS comment_count
    FROM comments
    GROUP BY comments.qid
)
SELECT questions.qid
    ,questions.question
    ,questions.date
    ,ISNULL(comment_summary.comment_count, 0) AS comment_count
FROM questions
LEFT JOIN comment_summary
    ON comment_summary.qid = questions.qid
ORDER BY questions.date

Or, if your SQL dialect, doesn't support CTEs:

SELECT questions.qid
    ,questions.question
    ,questions.date
    ,ISNULL(comment_summary.comment_count, 0) AS comment_count
FROM questions
LEFT JOIN (
    SELECT comments.qid
        ,COUNT(*) AS comment_count
    FROM comments
    GROUP BY comments.qid
) AS comment_summary
    ON comment_summary.qid = questions.qid
ORDER BY questions.date