February 6, 2015 at 8:44 am
Hi, I'm trying to count the number of scores in a column that are not null the cell is not blank. I know the COUNT function ignores NULLs already but I can't find anywhere how to make it also not count missing/blank cells. If anyone could point me in the right direction. Thanks!
February 6, 2015 at 9:13 am
giszzmo (2/6/2015)
Hi, I'm trying to count the number of scores in a column that are not null the cell is not blank. I know the COUNT function ignores NULLs already but I can't find anywhere how to make it also not count missing/blank cells. If anyone could point me in the right direction. Thanks!
You can force an empty string to a NULL using NULLIF(YourColumn, ''). So COUNT(NULLIF(YourColumn, '')) should exclude empty strings in addition to NULL.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 6, 2015 at 9:25 am
Thanks! That worked!
February 6, 2015 at 9:38 am
You are quite welcome. Glad that worked for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 6, 2015 at 12:55 pm
Just out of curiosity, in this case wouldn't a filter do the job, something like WHERE XX <> '' or WHERE LEN(XX) > 0 or even better, WHERE XX > ''?
😎
February 6, 2015 at 12:57 pm
Eirikur Eiriksson (2/6/2015)
Just out of curiosity, in this case wouldn't a filter do the job, something like WHERE XX <> '' or WHERE LEN(XX) > 0 or even better, WHERE XX > ''?😎
Probably so. I was dealing with the symptom and you came along with the cure. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply