Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Counting NULL values in a Pivot table Expand / Collapse
Author
Message
Posted Wednesday, April 08, 2009 8:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 13, 2009 8:55 AM
Points: 3, Visits: 26
I have the following query:

select type, istatus, count(logical_name)
from dbo.devicem1
group by type, istatus
order by type, istatus

The possible values of istatus are:
ARCHIVED, AVAILABLE, BUILD, OPERATIONAL, ORDERED, RECEIVED, REQUESTED, Retired, TEST or the value can be NULL.

I am turning this into a Pivot table query and have the following working at this point:

select type as 'TYPE',
[ARCHIVED] as 'ARCHIVED',
[AVAILABLE] as 'AVAILABLE',
[BUILD] as 'BUILD',
[OPERATIONAL] as 'OPERATIONAL',
[ORDERED] as 'ORDERED',
[RECEIVED] as 'RECEIVED',
[REQUESTED] as 'REQUESTED',
[Retired] as 'Retired',
[TEST] as 'TEST',
[ARCHIVED]+[AVAILABLE]+[BUILD]+[OPERATIONAL]+[ORDERED]+[RECEIVED]+[REQUESTED]+[Retired]+[TEST] as TOTALS
from
(select type, istatus, logical_name
from dbo.devicem1
group by type, istatus, logical_name) p
pivot (count(logical_name)
for istatus in ([ARCHIVED],[AVAILABLE],[BUILD],[OPERATIONAL],[ORDERED],[RECEIVED],[REQUESTED],[Retired],[TEST])
) as DevicePivot
order by type

With the first basic query, I get a count back for the number of logical_names there are where istatus is NULL within a type. With the Pivot table query, I have not been able to replicate retrieving the count for the NULL values. I am fairly new to this level of SQL coding. Can anyone suggest how I can retrieve the NULL count in the second query?

Thanks.
Post #693198
Posted Wednesday, April 08, 2009 9:23 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
Have you tried using IsNull on the column? You might not be able to do that directly in the Pivot operator, but if you first use a CTE to query the table, and use IsNull in the CTE, then build the Pivot on the results of the CTE, you should be able to get what you need.

Does that help?


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #693251
Posted Wednesday, April 08, 2009 11:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 13, 2009 8:55 AM
Points: 3, Visits: 26
I have tried variations with IsNull to no avail. I haven't tried with a CTE first as I am not that familiar with it but I will check it out.
Thanks for the reply.
Post #693403
Posted Wednesday, April 08, 2009 12:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 13, 2009 8:55 AM
Points: 3, Visits: 26
I got it to work with the following code:

select type as 'TYPE',
[ARCHIVED] as 'ARCHIVED',
[AVAILABLE] as 'AVAILABLE',
[BUILD] as 'BUILD',
[OPERATIONAL] as 'OPERATIONAL',
[ORDERED] as 'ORDERED',
[RECEIVED] as 'RECEIVED',
[REQUESTED] as 'REQUESTED',
[Retired] as 'Retired',
[TEST] as 'TEST',
[BLANK] as 'NULL',
[ARCHIVED]+[AVAILABLE]+[BUILD]+[OPERATIONAL]+[ORDERED]+[RECEIVED]+[REQUESTED]+[Retired]+[TEST]+[BLANK] as TOTALS
from
(select type, isnull(istatus,'BLANK') as istatus, logical_name
from dbo.devicem1
group by type, istatus, logical_name) p
pivot (count(logical_name)
for istatus in ([ARCHIVED],[AVAILABLE],[BUILD],[OPERATIONAL],[ORDERED],[RECEIVED],[REQUESTED],[Retired],[TEST],[BLANK])
) as DevicePivot
order by type
Post #693418
Posted Wednesday, April 08, 2009 12:54 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
Cool biz. Always good to defeat one of these ones that looks daunting at first, isn't it?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #693473
Posted Wednesday, April 24, 2013 11:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 11:33 PM
Points: 1, Visits: 4
Thank you very much, this also sloves my problem :)
Post #1446302
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse