The Forum has been Updated
The code has been upgraded to the latest .NET core version. Please check instructions in the Community Announcements about migrating your account.
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?
"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
Code:
WHERE isApproved = falseI just can't get the syntax correct. Can someone help?
Posted
Try this:
Code:
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))
Posted
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)
It can alos be accomplished without using the having clause by doing somtheing like this
Code:
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)
Posted
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.
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.
Posted
NullIf works in MySql
Posted
Originally posted by ruiribthe 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
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.