October 10, 2012 at 4:39 am
Hi guys basically iv got a table thats looks like this
Learner nationality mode of study
123 gb 1
435 null 2
211 wq 3
234 re null
123 null null
i want to run a query that will allow me to count the number of learners that have null within the other fields
so i want it to look like this
Nationality mode of study
4 1
4 and 1 being number of learners with null in those respected fields
please hellllp
kind regards mamzy
October 10, 2012 at 5:07 am
this should work, and return the number of Nulls in the Nationalirty and ModeOfStudy columns, although the result set is 2,2 rather than the 4,1 that you mention in the opening post.
CREATE TABLE #tmp(Learner int, nationality varchar(10), modeofstudy int)
Insert into #tmp
Select 123, 'gb' ,1
Union Select 435, null ,2
Union Select 211, 'wq', 3
Union Select 234, 're', null
Union Select 123, null, null
Select y.y Nationality,x.x ModeOfStudy
From
(Select COUNT(*) y
from #tmp where nationality is Null)y
Cross apply (Select COUNT(*) x
from #tmp where modeofstudy is Null) x
_________________________________________________________________________
SSC Guide to Posting and Best Practices
October 10, 2012 at 5:12 am
SELECT COUNT(*), COUNT(nationality), COUNT(modeofstudy)
FROM (
Select 123, 'gb' ,1
Union Select 435, null ,2
Union Select 211, 'wq', 3
Union Select 234, 're', null
Union Select 123, null, null
) t (Learner, nationality, modeofstudy)
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply