SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Counting NULL values in a Pivot table


Counting NULL values in a Pivot table

Author
Message
mdonahue
mdonahue
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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.
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23309 Visits: 9730
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
mdonahue
mdonahue
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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.
mdonahue
mdonahue
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23309 Visits: 9730
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
MF Darvesh
MF Darvesh
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 4
Thank you very much, this also sloves my problem Smile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search