Post reply

Case and Count Problem

  • June 13, 2009 at 11:32 am

    #132879

    Hi All,

    Subjects Table

    FaceID TagID PersonClass

    1 1 1

    2 2 2

    3 3 3

    4 4 4

    History Table

    ID FaceID TagID

    1 1 1

    2 2 2

    3 1 1

    4 1 1

    5 3 3

    6 2 2

    7 1 1

    8 2 2

    when i query for personclass 1 it should return me 4 for personclass 2 it shoud return 3 and for personclass 3 it shoud return 1. but for all the classes it is returning me 8. below is the query

    select

    count(case when personclass=1 then HistoryID else 0 end) Face,

    count(case when personclass=2 then HistoryID else 0 end) RFID,

    count(case when personclass=3 then HistoryID else 0 end) Face_RFID

    FROM dbo.Subjects INNER JOIN dbo.History ON dbo.Subjects.Face_ID = dbo.History.Face_ID AND dbo.Subjects.Tag_ID = dbo.History.Tag_ID .

    can u please tel me where i am goin wrong in this case.

    thanks

    fairozkhan

  • June 13, 2009 at 11:36 am

    #1009127

    Change your counts to sum or change your 0 to NULL

    Count counts the number of rows, it doesn't care if the values in those rows are 1 or 0, either one counts as 1 row. It's only NULL that doesn't contribute to a count(expression)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 14, 2009 at 1:02 am

    #1009256

    thanks gila, it helped me.

    fairozkhan

Viewing 3 posts - 1 through 3 (of 3 total)

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