Have Geek Brain

Get total row count from another table with matching id

For my first table, I have questions like this:

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

and then I have comments on the table

cid | qid
 1     1
 2     1 
 3     1

so in my question table I could have a column added that had total_comments which would be three

I tried 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 is it errors and only grabs the first row when there is a row with a higher date? thanks in advance

0


a source to share


4 answers


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
0


a source


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
+1


a source 


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

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

SELECT COUNT(qid)
FROM comments
WHERE qid = 1
0


a source


You need to have "GROUP BY questions.qid, questions.question, questions.date" on your application before "ORDER BY".

0


a source






More articles:


All Articles