Counting NULL values in a Pivot table

  • 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.

  • 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

  • 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.

  • 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

  • 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

  • Thank you very much, this also sloves my problem 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply