| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2003-03-31 : 08:19:07
|
| Can count(ALL expression) be useed for counting rows where a column has a particular value?This worksselect MyName, case when Mycol=1 then 1 else 0 end as totalfrom MyTable group by MyNameThis doesn't workselect Myname, count(all Mycol=1) as totalfrom MyTable group by MyNameIs my understanding the syntax wrong?Sam |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2003-03-31 : 08:30:53
|
| Count won't do the processing required to determine if a column = 1. Try:select Myname, count(*) as total where mycol = 1from MyTable group by MyName HTH*#&#* *#&#* *#&#* *#&#* Chaos, Disorder and Panic ... my work is done here! |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-03-31 : 08:31:55
|
| If SQL allowed for boolean datatypes*, you could saySUM(fieldX = value)but in SQL, a=b will not return a value, just an error message. (actually, you'd say SUM(fieldx=value)* -1 to me more accurate.) One of the nice things about Access, actually, is that you can do this kind of thing.AS far as I know, the SUM(CASE..END) statement is the best/only way to do it.----------*disclaimer: I love SQL Server; I am not complaining about T-SQL or SQL Server in any way. It is a wonderful product and I am happy to be using it. I am not just pointing out difficiencies to upset those that love SQL Server, nor would I rather be using Oracle or Access, I am just answering questions and voicing my humble honest opinion.- JeffEdited by - jsmith8858 on 03/31/2003 08:32:48 |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-03-31 : 10:46:52
|
I don't know Jeff. Sounds to me like you have some attitude about T-SQL?  I appreciate the feedback, but what's the semantic and syntax of :COUNT (ALL expression)In particular, what kind of expression can appear after keyword ALL? (BOL defines count(ALL expression), but shows no examples)enquiring minds want to know  SamFrom BOL:expression - Is an expression of any type except uniqueidentifier, text, image, or ntext. Aggregate functions and subqueries are not permitted.Edited by - SamC on 03/31/2003 10:47:47Edited by - SamC on 03/31/2003 10:51:03 |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-03-31 : 11:09:53
|
| I actually do like T-SQL and SQL Server alot, but a couple of times I posted my thoughts about features that would be nice to have and certain people here at the forums get very upset at me, so I decided to include a disclaimer any time I even come close to mentioning a criticism of SQL.Actually, Sam, the expression can be any expression as it mentions in BOL. But as I mentioned, SQL does NOT treat =,>,<, etc as mathematical operators -- only as comparison operators. Thus, you cannot use them in an expression.*That is1 + 2 is a valid expression, result of 3.2 is a valid expression, result of 2.But2 = 2 is NOT a valid expression. In many programming lanuages it IS a valid expression, returning an result of -1 if true or 0 if false.Thus, you can sayCOUNT(ALL 1+2)COUNT(ALL Field1 + Field2)but notCOUNT(ALL Field1 = Field2)With our without that ALL, the same rules apply. it is just a field name or an expression; but comparision operators cannot be used in place of mathematical operators in T-SQL. Of course, you can simulate that by using CASE:CASE a=b THEN -1 ELSE 0 ENDis the T-SQL equivalent ofa=bin other languages such as Access or VB.-----* see previous disclaimer; this is not meant to be a criticism of SQL server or T-SQL or microsoft in any way.- Jeff |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-03-31 : 11:19:50
|
| I'm tempted to say: "It sure would be great for SQL to support..."But I think I'll pass. Anyway thanks for clearing up the expression conundrum for me.Sam |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-03-31 : 12:03:59
|
| By the way --In T-SQL, another way without using case to do what we talked about could be:ABS(SIGN(Value1 - Value2))That will return 1 if Value1 = Value2, 0 otherwise. Of course, this will only work for numeric values. But you can do some pretty neat things like this w/o using case expressions. Might even be faster, too, but I have no idea.- Jeff |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-03-31 : 13:25:54
|
My trouble with count(expression) just dawned on me.Given that expression cannot be a logical condition in count....It is meaningless to have an expression in count at all  count(MyCol), count(MyCol+1), count(MyCol*2) all return the same count.sum(MyCol), sum(MyCol+1), sum(MyCol+2) work well with expressions.Does anyone have an example of count(expression) being useful?Sam |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-03-31 : 14:15:08
|
| COUNT(expr) will count up all occurancies of non-Nulls for expr. So, you can count the # of times the expression does not evaluate to Null.I just thought, you could use the NULLIF() function in this case:COUNT(NullIF(Expr1,Expr2))that would return the # of times expr1 is NOT equal to expr2.can't think of much else... I use COUNT(*) or even SUM(1) or SUM(CASE) quite a bit, almost never COUNT(expr).- Jeff |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-03-31 : 14:18:09
|
| select count(distinct datepart(dw,crdate))from sysobjectsOK, not very useful but gives the number of used weeks.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-03-31 : 21:05:48
|
| You can use Count() with a CASE expression to do the kind of counting you're looking for:set nocount oncreate table #d(id int null)insert #d select 1insert #d select nullinsert #d select 2insert #d select 1insert #d select 1insert #d select 1select count(id) NonNulls, count(*) AllRows, count(CASE ID when 1 THEN 1 END) AllOnes,count(CASE ID when 2 THEN 2 END) AllTwos from #ddrop table #dThis relies on Count()'s behavior of ignoring nulls, so the actual expression being counted doesn't matter as long as it's not null. You can achieve the same thing using SUM...CASE...1...ELSE 0, but you can also do some extra tricky things with nested CASE expressions, or other columns that might yield nulls, that allow you to combine 2 or more counts in one, or just provide some simplified logic. And as Nigel pointed out, Count(DISTINCT x) is pretty handy, for me it's utterly priceless, I use it constantly, and there's no other way to accomplish the same results as easily. |
|
|
|
|
Count (ALL expression)