| Author |
 Topic  |
|
|
TastyNutz
Junior Member
USA
251 Posts |
 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? |
PowerQuad Disability Support Forum |
Edited by - TastyNutz on 25 August 2011 23:28:39 |
|
|
Carefree
Advanced Member
Philippines
4212 Posts |
Posted - 26 August 2011 : 02:46:51
|
Try this:
|
 |
|
|
HuwR
Forum Admin
United Kingdom
20593 Posts |
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) |
MVC .net dev/test site | MVC .net running on Raspberry Pi |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Carefree
Advanced Member
Philippines
4212 Posts |
Posted - 26 August 2011 : 10:10:03
|
| NullIf works in MySql |
|
|
|
HuwR
Forum Admin
United Kingdom
20593 Posts |
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 |
MVC .net dev/test site | MVC .net running on Raspberry Pi |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
HuwR
Forum Admin
United Kingdom
20593 Posts |
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 |
MVC .net dev/test site | MVC .net running on Raspberry Pi |
|
|
|
TastyNutz
Junior Member
USA
251 Posts |
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... |
PowerQuad Disability Support Forum |
|
|
| |
Topic |
|
Count from SQL statement with a JOIN
