please help me in this query ( add condition to count())
Posted by: nasser bahaj
Date: January 06, 2010 08:05PM

hi guys.

please i need your help for this mysql query

/////
table 1 (posts):

postid > auto-increment
catid > int
posttitle > varchar
posttext > text
active > int // 1=active , 0=inactive
//////

//////
table 2 : (categories):

catid > auto-increment
catname > varchar
//////:

so i want to list categories by category name and i want to put beside every category the number of posts in it . i used this query :

SELECT categories.catname,COUNT(posts.postid) AS numposts FROM categories LEFT JOIN posts ON categories.catid=posts.catid GROUP BY categories.catname


this query is working well but the problem is i want to count only the active posts (where active = 1 ) i want to add this condition to count , i tried this query .

SELECT categories.catname,COUNT(posts.postid) AS numposts FROM categories LEFT JOIN posts ON categories.catid=posts.catid WHERE posts.active=1 GROUP BY categories.catname

but the problem at this query it is select only the categories that has active posts while i want to select all categories but count only ative posts

thanks for your time ..

Options: ReplyQuote


Subject
Written By
Posted
please help me in this query ( add condition to count())
January 06, 2010 08:05PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.