October 24, 2011 at 5:56 pm
I'm looking for a script that will do a count for every column in a table including the number of null values
October 24, 2011 at 7:29 pm
Is this what you require?
CREATE TABLE #T(
[NationalIDNumber] [nvarchar](15) NULL,
[ContactID] [int] NULL,
[ManagerID] [int] NULL)
INSERT INTO #T
SELECT 'GOT',NULL,NULL UNION ALL
SELECT NULL,1,NULL UNION ALL
SELECT NULL,NULL,2 UNION ALL
SELECT NULL,NULL,NULL
SELECT COUNT(*) AS 'All rows',COUNT(ContactID) as 'count of ContactID' FROM #T
Results:
All rows count of ContactID
4 1
DROP TABLE #T
October 24, 2011 at 8:30 pm
Well kinda. This table has 75 columns in it and I have to show two values for one column: 1) count where the column is not null, 2) where the column is null:
example
column count of values count of nulls
column1 10 5
column2 100 10
October 24, 2011 at 9:43 pm
Getting close to midnight for me, and my brain is getting tired. But and it is a big but, you can retrieve all the column names for a table by
using:
SELECT Column_Name FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = 'T' AND IS_Nullable = 'Yes'
I created a table T with the same column definitions in my Temp DB and had returned
NationalIDNumber
ContactID
ManagerID
Now running out of brain power, sorry for that, next step is to save the column names, and use them to create a dynamic T-SQL statement.
I know is is not enough, but hope it gets you started on a solution.
October 26, 2011 at 6:06 am
Still kinda vague what you're asking for. You mean that you want counts for EACH column in the table (and not "one column")? In your example, why do you show a total of 15 (ten not NULL and five NULL) for Column1 while the count for Column2 is 110? Wouldn't the totals always sum up to the number of records in the table (either they are NULL or they are not)?
Column Name Count NULL Count Other
----------- ---------- -----------
Column1 105 5
Column2 95 15
...
Column75 100 10
Clarity will get you an answer faster...
October 26, 2011 at 7:27 am
ACinKC (10/26/2011)
Still kinda vague what you're asking for. You mean that you want counts for EACH column in the table (and not "one column")? In your example, why do you show a total of 15 (ten not NULL and five NULL) for Column1 while the count for Column2 is 110? Wouldn't the totals always sum up to the number of records in the table (either they are NULL or they are not)?
Column Name Count NULL Count Other
----------- ---------- -----------
Column1 105 5
Column2 95 15
...
Column75 100 10
Clarity will get you an answer faster...
The other values in the table can be empty also (IS NULL)
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
October 27, 2011 at 12:25 pm
As promised on 10/24 finally found some free time. Now this code is not the fastest, and more than likely could be improved, but it does what I think you required. My table T would of course be your actual table with your data. I created my table (T) to test and illustrate the code.
CREATE TABLE T([NationalIDNumber] [nvarchar](15) NULL,[ContactID] [int] NULL,[ManagerID] [int] NULL)
INSERT INTO T
SELECT 'xxx',NULL,NULL UNION ALL
SELECT 'GOT',NULL,NULL UNION ALL
SELECT NULL,1,NULL UNION ALL
SELECT NULL,NULL,2 UNION ALL
SELECT NULL,2,NULL UNION ALL
SELECT NULL,1,NULL UNION ALL
SELECT NULL,2,NULL UNION ALL
SELECT NULL,NULL,NULL
--to hold results
CREATE TABLE Tempdb..R(Coln VARCHAR(20),Count_Other INT,Count_Null INT)
--------Preperation complete
-- Now develoe and execute the dynamic T-SQL statements
SET NOCOUNT ON
DECLARE @ColName VARCHAR(50)
DECLARE @message VARCHAR(200)
DECLARE @Sql NVARCHAR(200) --size may have to be increased
DECLARE Col_cursor CURSOR FOR
SELECT Column_Name FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = 'T'
ORDER BY Column_Name
OPEN Col_cursor
FETCH NEXT FROM Col_cursor
INTO @ColName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Sql = 'SELECT '''
SET @Sql = @Sql + @ColName +''',(COUNT(*)-(COUNT(*)-COUNT('+ @ColName + '))), + COUNT(*)-COUNT(' + @ColName + ') FROM T'
--PRINT @Sql --Used for debugging
INSERT INTO Tempdb..R
EXECUTE sp_executesql @Sql
FETCH NEXT FROM Col_cursor
INTO @ColName
END
SELECT * FROM tempdb..R
CLOSE col_cursor
DEALLOCATE col_cursor
SELECT * FROM T
SELECT * FROM Tempdb..R
--Cleanup for possible re-run
-- DROP TABLE Tempdb..R
Results:
Col name Count_Other Count_Null
ContactID 4 4
ManagerID 1 7
NationalIDNumber26
October 27, 2011 at 5:39 pm
Thanks for all of your hard work
October 28, 2011 at 5:31 am
ericwenger1 (10/27/2011)
Thanks for all of your hard work
And thank you for replying, after my taking so long to respond I was afraid that you might miss the post. Hope it helped you and might assist others.
August 9, 2017 at 2:08 pm
how can you add a where to the above.
So it only counts fields for value x in field a.
select count(total) from tblt where tblt.name = 'name'
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply