Q. What is the SQL query to find the count of records in a table that have a NULL value in a column?


Ans. If you want to find this in any specific column, it’s pretty straightforward…

select count(*) from sometab where somecol IS NULL;

or

select count(somecol) from sometab;

In the latter, each record that has a somecol that is non-NULL will add one to the count, or zero otherwise.

If you want to find a count of records where _any_ column is NULL, unfortunately there aren’t any ways that avoid just listing the columns and OR IS NULL stuff (or something logically similar), ie

  1. select count(*) from sometab where col1 IS NULL or col2 IS NULL or ... or colN IS NULL; 


 

Comments

Post a Comment

Popular posts from this blog

SQL Assignment – Module 4

                          SQL Assignment – Module 4  SQL Assignment – Module 4 Questions : 1. What is the cost of the costliest software development in Basic?  2. Display details of Packages whose sales crossed the 2000 Mark.  3. Who are the Programmers who celebrate their Birthdays during the Current Month?  4. Display the Cost of Package Developed By each Programmer.  5. Display the sales values of the Packages Developed by each Programmer.  6. Display the Number of Packages sold by Each Programmer.  7. Display each programmer’s name, costliest and cheapest Packages Developed by him or her.  8. Display each institute name with the number of Courses, Average Cost per Course.  9. Display each institute Name with Number of Students.  10. List the programmers (form the software table) and the institutes they studied.  11. How many packages were developed by students, who...
Read more

SQL Assignments TABLE

Assignments TABLE CREATE TABLE STUDIES (     PNAME VARCHAR(500),     INSTITUTE VARCHAR(500),     COURSE VARCHAR(500),     --COURSE_FEE decimal (10,2) ----------or COURSE_FEE int ); INSERT INTO STUDIES (PNAME, INSTITUTE, COURSE, COURSE_FEE) VALUES     ('ANAND', 'SABHARI', 'PGDCA', 4500),     ('ALTAF', 'COIT', 'DCA', 7200),     ('JULIANA', 'BDPS', 'MCA', 22000),     ('KAMALA', 'PRAGATHI', 'DCA', 5000),     ('MARY', 'SABHARI', 'PGDCA', 4500),     ('NELSON', 'PRAGATHI', 'DAP', 6200),     ('PATRICK', 'PRAGATHI', 'DCAP', 5200),     ('QADIR', 'APPLE', 'HDCA', 14000),     ('RAMESH', 'SABHARI', 'PGDCA', 4500),     ('REBECCA', 'BRILLIANT', 'DCAP', 11000),     ('REMITHA', 'BDPS', 'DCS', 6000),     ('REVATHI', 'SABHARI...
Read more

SQL Assignment – Module 1

SQL Assignment – Module 1                         SQL Assignment – Module 1  SQL Assignment – Module 1 Questions Problem Statement: Consider yourself to be Sam and you have been given the below tasks to complete using the Table – STUDIES, SOFTWARE & PROGRAMMER.  1. Find out the selling cost AVG for packages developed in Pascal.  2. Display Names, Ages of all Programmers.  3. Display the Names of those who have done the DAP Course.  4. Display the Names and Date of Births of all Programmers Born in January.  5. Display the Details of the Software Developed by Ramesh.  6. Display the Details of Packages for which Development Cost have been recovered.  7. Display the details of the Programmers Knowing C.  8. What are the Languages studied by Male Programmers? 9 . Display the details of the Programmers who joined before 1990.  10. Who are the authors of the Packages, which have ...
Read more