2

I have an SQL query where I simply join two tables. One table contain comments and the other is the user table. I join the tables to in a simple manner get user information about the user who wrote the comment (username) at the same time get the comment (comment text etc.).

Now I want to count the number of comments to write the correct number of comments on the top of the page. I do this by adding a COUNT, and an alias to save the value.

When I echo numCount, I get the correct value of comments, but I get no comments in my comment loop. As soon as I remove the count, I get all comments again. What am I doing wrong?

SELECT
ncID, ncText, ncDate,
uID, uName, uImageThumb,
COUNT(a.ncID) AS numComments
FROM tblNewsComments a LEFT JOIN tblUsers b
ON a.ncUserID = b.uID
WHERE  a.ncNewsID = $newID 
ORDER BY ncDate DESC
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
David
  • 49
  • 1
  • 1
  • 8

4 Answers4

3

I am going to assume this is MySQL (or maybe SQLite), since most other RDBMS would fail on this query. The issue is that you are missing a GROUP BY clause, which is required when using an aggregate function like COUNT() unless it is to operate over the entire rowset. MySQL's unusual behavior is to allow the absence of a GROUP BY, or to allow columns in SELECT which are not also in the GROUP BY, producing unusual results.

The appropriate way to do this would be to join in a subquery which returns the COUNT() per ncID.

SELECT
  ncID,
  ncText,
  ncDate,
  uID,
  uName,
  uImageThumb,
  /* The count returned by the subquery */
  ccount.numComments
FROM
  tblNewsComments a
  LEFT JOIN tblUsers b ON a.ncUserID = b.uID
  /* Derived table returns only ncID and count of comments */
  LEFT JOIN (
    SELECT ncID, COUNT(*) AS numComments 
    FROM tblNewsComments 
    GROUP BY ncID
  ) ccount ON a.ncID = ccount.ncID
WHERE a.ncNewsID = $newID
ORDER BY ncDate DESC

Edit Whoops - looks like you wanted the count per ncID, not the count per ncUserID as I originally had it.

Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
0

I don't know what SQL engine you are using, but what you have here is not valid SQL and should be flagged as such.

COUNT is an aggregate function and you can only apply those to groups or a whole table, so in your case you would probably do

SELECT
ncID, ncText, ncDate,
uID, uName, uImageThumb,
COUNT(a.ncID) AS numComments
FROM tblNewsComments a LEFT JOIN tblUsers b
ON a.ncUserID = b.uID
WHERE  a.ncNewsID = $newID 
GROUP BY ncID, ncText, ncDate,
uID, uName, uImageThumb
ORDER BY ncDate DESC
500 - Internal Server Error
  • 28,327
  • 8
  • 59
  • 66
  • Thank you! I tried this, but for some reason it only gives me ONE comment, both when I try to loop out and in the count. Any ideas what could be wrong? – David Feb 09 '13 at 01:51
  • I don't know your table structure, obviously, but ncID should probably not be part of the selection list nor the group by - try removing it. That appears to be the comment ID, correct? – 500 - Internal Server Error Feb 09 '13 at 01:54
  • Yes, that is the comment ID (autoincrement) – David Feb 09 '13 at 01:55
  • Removing the comment's Id didn't do it...I'm writing out the count by: $values = mysql_fetch_assoc($comments); // Number of comments $num_rows = $values['numComments']; – David Feb 09 '13 at 01:57
0

You're using an AGGREGATE function (Count) but you're needing a GROUP BY to make any sense from that count.

I suggest adding "GROUP BY [all other field names except the COUNT]" to your query

es137custom
  • 21
  • 2
0

Try this:

SELECT  
ncID, ncText, ncDate,  
uID, uName, uImageThumb,  
(SELECT COUNT(ncID)   
FROM   
tblNewsComments a  
INNER JOIN  
tblUsers b  
ON a.ncUserID = b.uID)   
AS numComments  
FROM tblNewsComments a LEFT JOIN tblUsers b  
ON a.ncUserID = b.uID  
WHERE  a.ncNewsID = $newID   
ORDER BY ncDate DESC
Twifty
  • 3,267
  • 1
  • 29
  • 54
rplusm
  • 33
  • 5