"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
WHERE isApproved = false
I just can't get the syntax correct. Can someone help?
SELECT T_COMMENTS.ISAPPROVED, COUNT(*) AS CNT, T_WEBLOG.ID, T_WEBLOG.B_PUBLISHED FROM T_WEBLOG INNER JOIN T_COMMENTS ON T_WEBLOG.ID = T_COMMENTS.C_BID_FK WHERE T_COMMENTS.ISAPPROVED='FALSE' GROUP BY T_WEBLOG.ID, T_COMMENTS.ISAPPROVED, T_WEBLOG.B_PUBLISHED HAVING (((T_WEBLOG.B_PUBLISHED)=TRUE))
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
SELECT T_WEBLOG.ID, COUNT(NULLIF( T_COMMENTS.ISAPPROVED, 'False' )) AS UNAPPROVEDCNT, COUNT(NULLIF( T_COMMENTS.ISAPPROVED, 'True' )) AS APPROVEDCNT
FROM T_WEBLOG INNER JOIN T_COMMENTS ON T_WEBLOG.ID = T_COMMENTS.C_BID_FK
WHERE(((T_WEBLOG.B_PUBLISHED)=TRUE))
GROUP BY T_WEBLOG.ID
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):
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):
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
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.
Count from SQL statement with a JOIN
Posted - 25 August 2011 : 23:21:35
