asked
Counts and Aggregates
I have to provide counts and min/max on source data. Data has blank, NULL and non null values. What would be the best script to use to do this on each column in table quickly? If there are too many values in a column, say 1000 different values, in 1 column. They want min/max of values in that column. What would you typically do with Nulls or blanks in the this case. Thanks!
Gosh..,. This is certainly possible to do with dynamic SQL but what on this good Green Earth will they do with such information? And what do they want to do about LOB data?
One more question... is all of the data going to be VARCHAR/NVARCHAR or will there be numeric and temporal data types involved, as well?
Perfect. Then my answer below should do for a single column. We'll need to update it for dynamic SQL to do a whole table.
First, please see my comments on your question. Second, the following is POP Code (Proof of Principle) for how we'd need to handle VARCHAR, CHAR, NVARCHAR, or NCHAR columns for one column. --============================================================================================ -- Create a test table to demonstrate the solution with. -- This is NOT a part of the solution. --============================================================================================ --===== If the Temp Table exists, drop it IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL DROP TABLE #TestTable ; GO --===== Create and populate the test table on-the-fly SELECT TOP 1000000 SomeID = IDENTITY(INT,1,1) ,SomeColumn = RIGHT(NEWID(),3) INTO #TestTable FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2 ; --===== Update some rows to be NULL or Blank UPDATE #TestTable SET SomeColumn = CASE WHEN SomeID % 37 = 0 THEN NULL WHEN SomeID % 83 = 0 THEN SPACE(3) END WHERE SomeID % 37 = 0 OR SomeID % 83 = 0 ; --============================================================================================ -- Solve the problem for the given column --============================================================================================ SELECT NullCount = SUM(CASE WHEN SomeColumn IS NULL THEN 1 ELSE 0 END) ,BlankCount = SUM(CASE WHEN SomeColumn = '' THEN 1 ELSE 0 END) ,ValueCount = SUM(CASE WHEN SomeColumn > '' THEN 1 ELSE 0 END) --Not Null or Blank ,MinValue = MIN(CASE WHEN SomeColumn > '' THEN SomeColumn END) ,MaxValue = MAX(CASE WHEN SomeColumn > '' THEN SomeColumn END) ,UniqueCount = COUNT(DISTINCT SomeColumn) ,TotalCount = COUNT(*) FROM #TestTable ) SELECT ColName = 'SomeColumn' ,NULLCOUNT ,NULLPCT = CONVERT(DECIMAL(4,1),NULLCOUNT *100.0/TOTALCOUNT) ,BLANKCOUNT ,BLANKPCT = CONVERT(DECIMAL(4,1),BLANKCOUNT *100.0/TOTALCOUNT) ,VALUECOUNT ,VALUEPCT = CONVERT(DECIMAL(4,1),VALUECOUNT *100.0/TOTALCOUNT) ,MINVALUE ,MAXVALUE ,UNIQUECOUNT ,UNIQUEPCT = CONVERT(DECIMAL(4,1),UNIQUECOUNT*100.0/TOTALCOUNT) ,TOTALCOUNT ,TOTALPCT = CONVERT(DECIMAL(4,1),TOTALCOUNT *100.0/TOTALCOUNT) FROM CTEPREAGG ;
18 People are following this question.
