February 5, 2010 at 9:12 am
Hi, I'm trying to find a way to count columns which are not NULL or blank in a table and have hit a problem. This seems to be down to the datatype of the column. I've developed SQL which counts the populated columns in any table and returns a list of column names and the count of values, this works fine on char and int columns, but it fails when the column is numeric/decimal.
The example below is a simplified version of my problem, I know that here I could solve it using SUM(CASE WHEN column1 is not NULL then 1 else 0 end) + SUM(CASE WHEN column1 <> '' then 1 else 0 end) but I'm trying to develop something generic which could be used on any table where the user doesn't need to input column names.
Is there an alternative to NULLIF that would work here? any advice gratefully received - thanks.
create table #test
(column1 char(1) null,
column2 int null,
column3 decimal(6,2) null)
insert into #test
values('A', 1, 100.5)
insert into #test
values('',2,100.1)
insert into #test
values('C',null,100.2)
insert into #test
values('D', 4, null)
select count(nullif(column1,''))
from #test
select count(nullif(column2,''))
from #test
select count(nullif(column3,''))
from #test
the first two selections correctly return 3, the final one returns "Error converting data type varchar to numeric."
Thanks for reading - hope my problem is clear.
February 5, 2010 at 9:26 am
A column cannot be NULL, you must have a name for the column or it does not exist. Are you sure that you mean column, and not a row?
If you want to select the count the number of rows in a column then do this:
SELECT COUNT(*) FROM <TableName>
= 1555
If you use the columnname, it will ignore all NULL values
SELECT COUNT(MiddleName) FROM <TableName>
= 425
Now you can get the entire list of column name from the sys.columns table. Using that, you can get a count of each column in each table
Andrew SQLDBA
February 5, 2010 at 2:15 pm
Try these for correct counts based on your insert code. Row counts of 3 being returned are incorrect BTW. You insert one null in column2 and one null in column3, none in column1.
select count(*)
from #test
where column1 is null
select count(*)
from #test
where column2 is null
select count(*)
from #test
where column3 is null
Edit: The nullif fails because you are compare a blank value ("") to a numeric value.
-- You can't be late until you show up.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply