Post reply

Counting Nulls

  • June 5, 2008 at 6:50 am

    #188715

    hi

    could i have a bit of help with aggregate functions. below is my table and my desired output

    my table with headings at the top

    PersonId, Value

    1, NULL

    1, NULL

    1, NULL

    1, 6

    1, 6

    2, NULL

    2, NULL

    2, 8

    2, 9

    desired output

    PersonId, NullCount

    1, 3

    2, 2

    Obviously i just want to count the number of nulls.

    I have tried the following, but the count value is null (not 3 and 2):

    SELECT personId, CASE value2 WHEN NULL THEN COUNT(*) END AS NullCount

    FROM dbo.Table_1

    GROUP BY personId

    thanks

    pete

  • June 5, 2008 at 6:56 am

    #824762

    select PersonId, count(PersonId) as NullCount

    from dbo.Table_1

    where value is null

    group by PersonId

  • June 5, 2008 at 7:20 am

    #824784

    Sorry i should have been more specific - i was trying to break the problem down into the part i was stuck with. this is actually the result table i want

    personId, CountPersonId, CountNull

    1, 5, 3

    2, 4, 2

    thanks

    pete

  • June 5, 2008 at 7:37 am

    #824798

    There may be a more elegant solution for the nulls, but this should work

    select PersonId, count(PersonId), (count(PersonId)- count(Value)) as CountNulls

    from dbo.Table_1

    group by PersonId

  • June 5, 2008 at 8:07 am

    #824820

    The plot thickens...sorry i didn't want to make to too complicated to begin with but what if i have this table

    PersonId, Value, AltValue

    1, NULL, 6

    1, NULL, 6

    1, NULL, NULL

    1, 6, NULL

    1, 6, 6

    2, NULL, 6

    2, NULL, 6

    2, 6, 6

    2, 6, NULL

    and want to count where either value or altvalue are null

    i.e. a result of

    PersonId, PersonCount, NullCount

    1, 5, 4

    2, 4, 1

    thanks - and sorry again for the elongated question

    Pete

  • June 5, 2008 at 8:20 am

    #824831

    select PersonId, count(PersonId), (count(PersonId)- count(Value+AltValue)) as CountNulls

    The (Value + AltValue) will result in a null if either is null

  • June 5, 2008 at 8:47 am

    #824853

    brilliant! thank you

  • June 5, 2008 at 10:21 am

    #824947

    an alternate solution:

    create table #test(PersonID int, [Value] int, Altvalue int)

    Insert INTO #test

    Select 1, null, 6

    union all select 1, null, 6

    union all select 1, null, 6

    union all select 1, null, null

    union all select 1, null, 6

    union all select 2, null, null

    union all select 2, null, 6

    union all select 2, 6, 6

    Select personid,count(*) PersonCount,

    sum(case when coalesce([Value], AltValue) is null then 1 else 0 end) NullCount

    From #test

    group by personid

    drop table #test

Viewing 8 posts - 1 through 7 (of 7 total)

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