October 28, 2015 at 7:52 am
Hello All:
I know I can run a query one at a time, column by column, to do this, but I have to feel there is a way to do it all at once and hoping someone could help.
Lets says I have tableX with columns colA, colB, colC, colD and there are 2256 rows in the tableX.
I would like to find out the percentages of colA, colB, colC, colD that hold data (where it is not an empty string or NULL value).
So out of 2256 rows in the table, the user has stored data in colA 1987 times, colB 2250 times, colC 2256 times and colD 17 times.
So the report would say:
colA: 88.07%
colB: 99.73%
colC: 100%
colD: 0.01%
We have an application that has a bunch of fields that we believe are not being used and would like to remove them, but we need to prove this by looking at the data.
Hopefully this makes sense. I know I could run a query, one at a time and change the column name, but this would take a long time as there are a lot of columns in this table. I am hoping there is some way to do this in one query.
October 28, 2015 at 8:03 am
Something like the following
create table #test (cola bit, colb bit, colc bit, cold bit)
insert into #test values
(1,1,1,1),
(1,null,1,1),
(1,null,null,1),
(1,null,null,null),
(1,null,null,null),
(1,null,1,null)
select * from #test
select count(*), count(cola), count(colb), count(colc), count(cold) from #test
select 'colA : '+convert(varchar, count(cola)*100.0 / count(*))+'%' AS Result from #test
union
select 'colB : '+convert(varchar, count(colb)*100.0 / count(*))+'%' from #test
union
select 'colC : '+convert(varchar, count(colc)*100.0 / count(*))+'%' from #test
union
select 'colD : '+convert(varchar, count(cold)*100.0 / count(*))+'%' from #test
order by Result
drop table #test
October 28, 2015 at 8:29 am
I use
SUM(CASE WHEN MyColumn IS NULL THEN 0 ELSE 1 END) AS [Count_MyColumn]
rather than
COUNT(MyColumn) AS [Count_MyColumn]
because the latter produces a "Null value eliminated by aggregate" warning (unless using some fancy ANSI setting) and some APP code may treat the presence of such warnings as an error.
Also, with the CASE approach you could also do:
SUM(CASE WHEN MyColumn IS NULL [highlight="#ffff11"]OR MyColumn = ''[/highlight] THEN 0 ELSE 1 END) AS [Count_MyColumn]
to exclude "empty" columns from the Count, as well as NULL ones. (Could use the same approach to ignore ZERO values in numeric columns, etc.)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply