CODEWITHSUNDEEP
sqlcountaggregate
Tom Gullen
Tom Gullen

Reputation: 61727

Stuck on SQL query to count rows

SELECT 
  COUNT(*) AS TotalUserVotes
FROM tblArticleVotes AS v 
INNER JOIN tblArticles AS a 
  ON v.ArticleID = a.ID 
     AND a.AuthorID = 13405

This always returns 0. I have a table tblArticles which has an AuthorID and ID (primary key for article).

I then have an article votes table, with an articleID column.

Given user 13405 I need to find out how many votes they have across all of their articles!

Just to confirm, there is currently one record in tblArticleVotes which has the articleID of 5. Article ID 5 has the author ID of 13405.

Upvotes: 1

Views: 359

Answers (4)

Al W
Al W

Reputation: 7713

There is nothing wrong with your query. To trouble shoot this break the query up.

DECLARE @ArticleID int
SELECT @ArticleID = a.ID FROM tblArticles a WHERE a.AuthorID=13405

PRINT @ArticleID
SELECT * FROM tblArticleVotes where ArticleID=@ArticleID

Upvotes: 2

SingleNegationElimination
SingleNegationElimination

Reputation: 156158

try this instead:

SELECT a.AuthorID, COUNT(v.id) as votes
FROM tblArticles AS a 
LEFT JOIN tblArticleVotes AS v ON a.ID = v.ArticleID
GROUP BY a.ID
WHERE a.AuthorID = :author_id

Upvotes: 1

Yiangos
Yiangos

Reputation: 267

Try changing the "AND" to "WHERE":

SELECT COUNT(*) AS TotalUserVotes
FROM tblArticleVotes AS v INNER JOIN
tblArticles AS a ON v.ArticleID = a.ID WHERE a.AuthorID = 13405

HTH

Upvotes: 2

Erich Kitzmueller
Erich Kitzmueller

Reputation: 36987

Hmmm... the AuthorID in the JOIN looks odd... maybe this works better?

SELECT COUNT(*) AS TotalUserVotes
  FROM tblArticleVotes AS v INNER JOIN
  tblArticles AS a ON v.ArticleID = a.ID
  WHERE a.AuthorID = 13405

Upvotes: 4

Related Questions