COUNT() Function question
-
I need to find a way to use the COUNT() Function to count items in a column where the entry is "hello" and not "goodbye".
I dont want to use a CASE statement, as this will throw off the grouping of elements. Also, I have tried DerivedTables and that doesnt work either.
Truly hoping there is a way I can use the COUNT() function. Here is what I wish I could do:
SELECT COUNT(MyColumn, "Hello") AS HelloCount
Basically, I want to count items in a column when the item = "Hello" without using a case statement or using the where statement.
Is that possible?
-
Can you use having!!!
Why can't you use where or case in this particular statement?
-
I dont believe so. Here is a sample of my query:
SELECT UsersID, COUNT(SDDField) AS SDDField, COUNT(ETField) AS ETField, CASE WHEN CMPField = "Hello" THEN (SELECT COUNT(CMPField) FROM MyTable) END AS CMPField
FROM MyTable
GROUP BY UsersID
The Case statement counts the CMPField when it is Hello for all records, and Groups it. Here is an example of my results:
UsersID SDDField ETField CMPField
ID1 47 25 36
ID2 54 39 36
ID3 79 18 36
All of the fields count correctly, except the one in the CASE statement. I am not able to group within the CASE statement. If I could just COUNT the CMPField like I am the other fields I am counting, but add an IF clause or something like that, this would be golden.
Right now its pretty brown.
-
Try this.
SELECT UsersID, COUNT(SDDField) AS SDDField, COUNT(ETField) AS ETField, SUM(CASE WHEN CMPField = "Hello" THEN 1 ELSE 0 END) AS CMPField
FROM MyTable
GROUP BY UsersID
-
WOO HOO!! Thank you!
That worked like a charm!
-
If this in in a complex GROUP BY, forget COUNT... you have a condition and COUNT isn't going to do it for you... SUM(CASE will....
- SELECT your bunch of columns,
- SUM(CASE WHEN somecolumn = 'Hello' THEN 1 ELSE 0 END) AS CountOfHellos
- FROM yourtable
- GROUP BY your bunch of columns
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Sorry... didn't see Remi's solution, which is obviously spot on!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply