| T O P I C R E V I E W |
| TastyNutz |
Posted - 25 August 2011 : 23:21:35
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 WHERE isApproved = false
I just can't get the syntax correct. Can someone help? |
| 8 L A T E S T R E P L I E S (Newest First) |
| TastyNutz |
Posted - 26 August 2011 : 15:17:55
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... |
| HuwR |
Posted - 26 August 2011 : 12:35:49
quote:
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 |
| ruirib |
Posted - 26 August 2011 : 11:21:37
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.
|
| HuwR |
Posted - 26 August 2011 : 10:31:52
quote:
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  |
| Carefree |
Posted - 26 August 2011 : 10:10:03
NullIf works in MySql |
| ruirib |
Posted - 26 August 2011 : 09:51:43
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. |
| HuwR |
Posted - 26 August 2011 : 09:12:15
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) |
| Carefree |
Posted - 26 August 2011 : 02:46:51
Try this:
|
