CODEWITHSUNDEEP
sql-serversql-server-2008
John
John

Reputation: 107

How to calculate the percentage of non null columns in sql server?

How to calculate the percentage of non null columns in sql server?

Upvotes: 0

Views: 1549

Answers (1)

TT.
TT.

Reputation: 16137

If you meant "how many non-null values are there in my table in a certain column":

SELECT
  percentage=CASE WHEN (SELECT COUNT(*) FROM your_table)=0
    THEN 
      NULL
    ELSE
      CAST((SELECT COUNT(*) FROM your_table WHERE your_column IS NOT NULL) AS FLOAT)/
      (SELECT COUNT(*) FROM your_table)
  END

If you meant globally how many non null columns there are in all tables in your sql server instance:

SELECT
  percentage=
  CAST((
    SELECT
      COUNT(*)
    FROM
      INFORMATION_SCHEMA.COLUMNS
    WHERE
      IS_NULLABLE='NO'
  ) AS FLOAT)/
  (
    SELECT
      COUNT(*)
    FROM
      INFORMATION_SCHEMA.COLUMNS
  )

Upvotes: 1

Related Questions