I have the following statement:

"SELECT T_WEBLOG.id, T_COMMENTS.isApproved, T_WEBLOG.b_published FROM T_WEBLOG INNER JOIN T_COMMENTS ON T_WEBLOG.id = T_COMMENTS.c_bID_fk GROUP BY T_WEBLOG.id, T_COMMENTS.isApproved, T_WEBLOG.b_published HAVING (((T_WEBLOG.b_published)=True))"

I need to count isApproved
I just can't get the syntax correct. Can someone help?

Try this:

it is more efficient to use COUNT(1) rather than COUNT(*)

It can alos be accomplished without using the having clause by doing somtheing like this


That should result in something along the lines of

WEBLOGID | UNAPPROVEDCNT | APPROVEDCNT

There is no point in including PUBLISHED in the groupby or output since you are only asking for results where published = true.
obviosuly it is not tested as that would require your tables and data This may not work on mySQL as I have no idea if it supports NULLIF (doesn't work in access either)

A bit off discussion, but I am always interested in performance related issues. I always use COUNT(*) because I thought the optimizer would choose the best possible way to compute the count. Turns out that is true for several other variations, including COUNT(1):

http://thehobt.blogspot.com/2008/12/debunking-myth-select-count-vs-select.html

This is obviously valid for SQL Server. Not sure how other DBs handle this situation.

NullIf works in MySql

Originally posted by ruirib
A bit off discussion, but I am always interested in performance related issues. I always use COUNT(*) because I thought the optimizer would choose the best possible way to compute the count. Turns out that is true for several other variations, including COUNT(1):

http://thehobt.blogspot.com/2008/12/debunking-myth-select-count-vs-select.html

This is obviously valid for SQL Server. Not sure how other DBs handle this situation.

the most pertinant reason for using cont(1) is incase there are any null values in the column you are trying to count as it will not include them if there are, so just safer to use count(1) just incase

COUNT(*) counts NULLs, Huw: http://msdn.microsoft.com/en-us/library/ms175997.aspx

That does make sense too, as * is not really an expression, so the end result of it's use is that the query result will simply be the row count.
A quick google search will show that using COUNT(*) or COUNT(1) is implemented the same way for other DBs too, like Oracle.

Originally posted by ruirib
COUNT(*) counts NULLs, Huw: http://msdn.microsoft.com/en-us/library/ms175997.aspx

That does make sense too, as * is not really an expression, so the end result of it's use is that the query result will simply be the row count.
A quick google search will show that using COUNT(*) or COUNT(1) is implemented the same way for other DBs too, like Oracle.

cool, I'm obviously too used to older databases where (1) was more efficient than (*), better change my ways

Thanks everyone.
2 notes:
Carefree gave me what I was asking for. But turns out it's not quite what I need. Huwr, in your statement, both APPROVEDCNT and UNAPPROVEDCNT return only the total of T_COMMENTS.
I'm still working...