Pandas is offering
df.isnull().sum()
to count the NAN values per each column. Is there something similar in SQL?
Asked
Active
Viewed 2,297 times
2 Answers
1
No idea what Pandas is but good old CASE should do (and it works in all major database engines):
SELECT COUNT(CASE WHEN column_name IS NULL THEN 1 END) ...
Álvaro González
- 142,137
- 41
- 261
- 360
-
and ... continues with like `from table_name` basically – Murat Seker Aug 02 '17 at 12:16
-
Sure. But you manually need to specify all columns? Is there a solution where this is performed automatically? – Georg Heiler Aug 02 '17 at 15:22
-
That's another question. You need to look into dynamic SQL. – Álvaro González Aug 03 '17 at 08:23
1
You can use:
SELECT COUNT(*) - COUNT(column_name)
FROM table_name
COUNT(*) (or, equivalently, COUNT(1)) will count rows regardless of whether any columns have NULL values and COUNT(column_name) will count the non-NULL values (and, if it exists, can use an index on that column).
MT0
- 143,790
- 11
- 59
- 117
-
Sure. But you manually need to specify all columns? Is there a solution where this is performed automatically? – Georg Heiler Aug 02 '17 at 15:22
-
@GeorgHeiler How would the database know which columns you want to calculate the number of `NULL`s for if you did not specify the columns? There are no ways in SQL of repeating the same thing for all columns (unless you are going to use dynamic SQL and queries on the data dictionary but that is going to be less efficient). – MT0 Aug 02 '17 at 21:30
-
Is there no way to simply use ALL columns in a table? Maybe via PLSQL? – Georg Heiler Aug 02 '17 at 21:31
-
I will need to look into https://stackoverflow.com/questions/16528682/count-null-values-from-multiple-columns-with-sql and https://blogs.technet.microsoft.com/benjamin/2013/12/27/sql-tip-counting-null-values/ – Georg Heiler Aug 03 '17 at 07:30