May 7, 2015 at 3:27 pm
Hi all!
I have a table that I want to be able to count all null values and group it by a column without having to do a count(column) for each column? Is there a an easier way to do this?
Sample Data
EmployeeID Computers Phone Ipad
123 1 Null Null
124 Null 1 1
098 Null Null Null
I would like my output to be:
EmployeeId TotalNullValues
123 2
124 1
Thanks!
098 3
May 7, 2015 at 4:25 pm
Indeed there is.
Something like the following should do the trick.
I made some of the columns character data for no particular reason other than to throw a couple data types in there (and it seemed EmployeeID was probably character, since it had a leading 0).
--Let's get some sample data to work with
CREATE TABLE SampleData (
EmployeeID CHAR(3),
Computers char(1),
Phone int,
Ipad int
)
INSERT INTO SampleData
SELECT '123','1',NULL,NULL
UNION ALL
SELECT '124',NULL,1,1
UNION ALL
SELECT '098',Null,NULL,NULL
--Some CASE statements should make this simple
select EmployeeID,
sum(case WHEN Computers IS NULL THEN 1 ELSE 0 END)+
sum(case WHEN Phone IS NULL THEN 1 ELSE 0 END) +
sum(case WHEN Ipad IS NULL THEN 1 ELSE 0 END)
AS NumberOfNulls from SampleData
GROUP BY EmployeeID
--Lets clean up our sample table
DROP TABLE SampleData
Cheers!
May 8, 2015 at 9:21 am
Thanks for this!
I sometimes work with large data sets where my tables have over 100 columns. Is there an easier way of doing this instead of 100 case statements? Maybe a query join with sys.columns?
Thanks
May 8, 2015 at 10:00 am
Marv2011 (5/8/2015)
Thanks for this!I sometimes work with large data sets where my tables have over 100 columns. Is there an easier way of doing this instead of 100 case statements? Maybe a query join with sys.columns?
Thanks
You would have to do that with dynamic sql. My question is why do you care how many rows of each column have 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/
May 8, 2015 at 10:09 am
My question is why do you care how many rows of each column have NULL?
That does seem like an odd requirement.
-- Itzik Ben-Gan 2001
May 8, 2015 at 10:44 am
Its for research purposes. The table that I am analyzing is a back end table of a front end clinical survey. A null value means that question was skipped and not answered. My clinical practice needs to know how many questions are getting skipped. The survey unfortunately has 100 questions.
May 8, 2015 at 10:51 am
Marv2011 (5/8/2015)
Its for research purposes. The table that I am analyzing is a back end table of a front end clinical survey. A null value means that question was skipped and not answered. My clinical practice needs to know how many questions are getting skipped. The survey unfortunately has 100 questions.
I would think that with 100 columns it would be faster to just type out the columns as opposed to creating a dynamic sql solution for this. You could leverage sys.columns to help you build your query since the only thing that is really going to change across the columns is the column name.
select 'SUM(Case when [' + sc.Name + '] IS NULL then 1 else 0 end as [' + sc.Name + '_NullCount]'
from sys.columns sc
where object_id = object_id('SampleData')
Then just copy and paste the results into your query window in the correct location in the fine example code by Jason Wilkins.
_______________________________________________________________
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/
May 8, 2015 at 11:49 am
Since I'm incredibly lazy, I'll borrow some code from Old Hand and insert a shortcut:
--Let's get some sample data to work with
CREATE TABLE SampleData (
EmployeeID CHAR(3),
Computers char(1),
Phone int,
Ipad int
)
INSERT INTO SampleData
SELECT '123','1',NULL,NULL
UNION ALL
SELECT '124',NULL,1,1
UNION ALL
SELECT '098',Null,NULL,NULL
-- start my cheezy code
WITH this AS
(
SELECT EmployeeID, (SELECT * FROM SampleData sd2 WHERE sd.EmployeeID=sd2.employeeid FOR XML PATH, ELEMENTS XSINIL) AS xmlstr
FROM SampleData sd
)
SELECT EmployeeID, LEN(xmlstr) - LEN(REPLACE(xmlstr, 'xsi:nil="true"', 'xsi:nil="true'))
FROM this
-- end my cheezy code
--Some CASE statements should make this simple
select EmployeeID,
sum(case WHEN Computers IS NULL THEN 1 ELSE 0 END)+
sum(case WHEN Phone IS NULL THEN 1 ELSE 0 END) +
sum(case WHEN Ipad IS NULL THEN 1 ELSE 0 END)
AS NumberOfNulls from SampleData
GROUP BY EmployeeID
--Lets clean up our sample table
DROP TABLE SampleData
May 8, 2015 at 12:07 pm
Thank you everyone! Appreciate the input and help!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply