I have a bit of trouble on my hands. I am constructing a blog using PHP and PostGreSQL. I need to be able to call out the articles with the usernames and the number of comments for each article. Now, the articles and username I can do no problem, but the number of comments is making me mad (not really, but I can't think of a better word).
Right now, my SQL looks like:
SELECT greenblogger_users.username,
greenblogger_articles.*,
count(greenblogger_comments.cid)
WHERE greenblogger_articles.uid = greenblogger_users.uid
AND greenblogger_articles.nid = greenblogger_comments.nid;
The Error I get is: ERROR: Attribute greenblogger_users.username must be GROUPed or used in an aggregate function.
I've tried grouping it, but it doesn't work.
When you use an aggregate function like count or sum then you also need to add a GROUP BY clause which lists ALL table elements that aren't included in the count or sum aggregate function.
SELECT greenblogger_users.username,
greenblogger_articles.*,
count(greenblogger_comments.cid)
WHERE greenblogger_articles.uid = greenblogger_users.uid
AND greenblogger_articles.nid = greenblogger_comments.nid
GROUP BY greenblogger_users.username,
greenblogger_articles.uid,
greenblogger_articles.nid,
greenblogger_articles.name,
greenblogger_articles.date,
greenblogger_articles.article;
Now it produces nothing. o.o What am I doing wrong?
It doesn't even give you an error of some sort? Just an empty dataset?
Where is the FROM and how is the JOIN between the tables declared?
There aren't any. There don't need to be (to my knowledge)
Means select everything from greenblogger_articles and greenblogger_users.
Edit: >_< I keep forgetting that tab won't tab the data but it select the next form item.
Where is the FROM and how is the JOIN between the tables declared?
it's a generic join, defined by the where-clause.
Kurashu,
never use table.* in embedded sql !! always specify the fieldlist.
try
SELECT greenblogger_users.username,
greenblogger_articles.uid,
greenblogger_articles.nid,
greenblogger_articles.name,
greenblogger_articles.date,
greenblogger_articles.article;
count(*) as numrec
FROM (greenblogger_articles INNER JOIN greenblogger_users ON greenblogger_articles.uid = greenblogger_users.uid)
INNER JOIN greenblogger_comments ON greenblogger_articles.nid = greenblogger_comments.nid
GROUP BY greenblogger_users.username,
greenblogger_articles.uid,
greenblogger_articles.nid,
greenblogger_articles.name,
greenblogger_articles.date,
greenblogger_articles.article;
you can then get the number for each group with
$row['numrec']
Thanks, and I'll try to avoid *'s in generic table joins.