Count Values for Every Column in a Table

SQL: count number of distinct values in every column

I appreciate all of the responses. I think the solution that will work best for me in this situation (counting the number of distinct values in each column of a table from an external program that has no knowledge of the table except its name) is as follows:

Run "describe table1" and pull out the column names from the result.

Loop through the column names and create the query to count the distinct values in each column. The query will look something like "select count(distinct columnA), count(distinct columnB), ... from table1".

get a count of each value from every column in a table SQL Server

You could use:

DECLARE @Table SYSNAME = 'TableName'; 
DECLARE @SQL NVARCHAR(MAX) = ''
SELECT @SQL = STUFF((SELECT ' UNION SELECT ''' + name 
                            + ''' AS [Column], ' 
                            + 'CAST(' + QUOTENAME(Name)
                            + ' AS NVARCHAR(MAX)) AS [ColumnValue], COUNT(*) AS [Count] FROM ' 
                            + QUOTENAME(@Table) + ' GROUP BY ' + QUOTENAME(Name)
                    FROM   sys.columns 
                    WHERE  object_id = Object_id(@Table)
                    -- concatenate result strings with FOR XML PATH
                    FOR XML PATH ('')), 1, 7, '');

EXECUTE sp_executesql @SQL;

Which will produce SQL Like the following for a table with two columns (Column1 and Column2)

SELECT 'Column1' AS [Column], 
        CAST([Column1] AS NVARCHAR(MAX)) AS [ColumnValue], 
        COUNT(*) AS [Count] 
FROM    [TableName] 
GROUP BY [Column1] 
UNION 
SELECT  'Column2' AS [Column], 
        CAST([Column2] AS NVARCHAR(MAX)) AS [ColumnValue], 
        COUNT(*) AS [Count] 
FROM    [TableName] 
GROUP BY [Column2]

---

EDIT

If you want a new result set for each column then use:

DECLARE @Table SYSNAME = 'TableName'; 
DECLARE @SQL NVARCHAR(MAX) = '';
SELECT @SQL = (SELECT ' SELECT ' + QUOTENAME(Name) 
                        + ', COUNT(*) AS [Count] FROM ' 
                        + QUOTENAME(@Table) + ' GROUP BY ' + QUOTENAME(Name) + ';'
                FROM   sys.columns 
                WHERE  object_id = Object_id(@Table)
                -- concatenate result strings with FOR XML PATH
                FOR XML PATH (''));

EXECUTE sp_executesql @SQL;

Which would produce SQL Like:

SELECT  [Column1], 
        COUNT(*) AS [Count] 
FROM    [callsupplier] 
GROUP BY [Column1];

SELECT  [Column2], 
        COUNT(*) AS [Count] 
FROM    [callsupplier] 
GROUP BY [Column2];

How to count the amount of data in each column in a table

You can use a cursor with dynamic SQL that inserts each COUNT check on a temporary table.

You can control the schema, tables and columns to check with the cursor's SELECT.

IF OBJECT_ID('tempdb..#ColumnResults') IS NOT NULL
    DROP TABLE #ColumnResults

CREATE TABLE #ColumnResults (
    SchemaName VARCHAR(100),
    TableName VARCHAR(100),
    ColumnName VARCHAR(100),
    TotalRows INT,
    NotNullAmount INT)

DECLARE @SchemaName VARCHAR(100)
DECLARE @TableName VARCHAR(100)
DECLARE @ColumnName VARCHAR(100)

DECLARE ColumnCursor CURSOR FOR
    SELECT
        QUOTENAME(T.TABLE_SCHEMA),
        QUOTENAME(T.TABLE_NAME),
        QUOTENAME(T.COLUMN_NAME)
    FROM
        INFORMATION_SCHEMA.COLUMNS AS T
    WHERE
        T.TABLE_NAME = 'YourTableName' AND      -- Filter here the table you want to check
        T.TABLE_SCHEMA = 'YourTableSchema'      -- Filter here the schema you want to check
    ORDER BY
        T.TABLE_SCHEMA,
        T.TABLE_NAME,
        T.COLUMN_NAME

OPEN ColumnCursor
FETCH NEXT FROM ColumnCursor INTO 
    @SchemaName, 
    @TableName,
    @ColumnName

WHILE @@FETCH_STATUS = 0
BEGIN

    DECLARE @DynamicSQL VARCHAR(MAX) = '
        INSERT INTO #ColumnResults (
            SchemaName,
            TableName,
            ColumnName,
            TotalRows,
            NotNullAmount)
        SELECT
            SchemaName = ''' + @SchemaName + ''',
            TableName = ''' + @TableName + ''',
            ColumnName = ''' + @ColumnName + ''',
            TotalRows = COUNT(1),
            NotNullAmount = COUNT(' + @ColumnName + ')
        FROM
            ' + @SchemaName + '.' + @TableName + ' AS T'

    -- PRINT (@DynamicSQL)
    EXEC (@DynamicSQL)

    FETCH NEXT FROM ColumnCursor INTO 
        @SchemaName, 
        @TableName,
        @ColumnName

END

CLOSE ColumnCursor
DEALLOCATE ColumnCursor

SELECT
    C.*
FROM
    #ColumnResults AS C
ORDER BY
    C.SchemaName,
    C.TableName,
    C.ColumnName

You can comment the EXEC and uncomment the PRINT to check for the dynamic SQL created before executing.

Note that this will actually execute one SELECT for each column instead of a SELECT for all columns in a table. You could tamper the dynamic SQL a little so it works once per table while checking all columns, but I find this approach tidier and capable of working across schemas and tables on the same manner.

How can I count the number of zeros in all columns for a table in oracle sql

You can use:

WITH AA AS (
select 
col_1,col_2,col_3
from table_name
)

SELECT column_name, COUNT(column_name) zeros FROM
(
SELECT * FROM (

SELECT * FROM AA
UNPIVOT(
    zeros  
    FOR column_name 
    IN ( 
col_1,col_2,col_3
    )
)
) WHERE zeros = 0
) GROUP BY column_name

---

Related Topics