CODEWITHSUNDEEP
mysqlhibernate
sakar
sakar

Reputation: 243

Count returned by a Query when Particular column is null and not-null in MySql

i want to select a count returned by a query when a particular column is null and another query to select a count when that column is not null into single query how can i achieve it..?

i had tried some of the example that are avail in SOF but no use..

for example i want to 

select students count of class table where the address null and notnull

Upvotes: 0

Views: 465

Answers (4)

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

In MySQL this can do it

SELECT 
    SUM(IF(address IS NULL,1,0))       as  `Student_With_No_Address`,
    SUM(IF(address IS NOT NULL,1,0)) as    `Student_With_Address`
FROM students

SQL Fiddle Demo

Output :

Student_With_No_Address |   Student_With_Address
---------------------------------------------
        4               |           6

Upvotes: 3

rs.
rs.

Reputation: 27457

Try this

SELECT 
COUNT(CASE when address is null then 1 end) AS StudentsWithNoAddress,
COUNT(CASE when address is not null then 1 end) AS StudentsWithAddress 
FROM Class

Upvotes: 2

sakar
sakar

Reputation: 243

select 
 SUM( CASE when studentId is not NULL THEN 1 else 0 END ) as result1 ,
 SUM( CASE when studentId is NULL THEN 1 else 0 END) as result2
from class

Upvotes: 0

Ravindra Gullapalli
Ravindra Gullapalli

Reputation: 9178

You have to write two SELECT statements and combine them using UNION

SELECT 'No Address' AS AddressStatus, COUNT(*) AS NoOfStudents 
FROM Class WHERE Address IS NULL
UNION
SELECT 'With Address' AS AddressStatus, COUNT(*) AS NoOfStudents 
FROM Class WHERE Address IS NOT NULL

Upvotes: 1

Related Questions