Post reply

how to count column numbers (non-null values only) in each row

  • February 6, 2005 at 8:27 am

    #65349

    I'm having trouble to figure out a way to count my data.  Here is my scenario:

    Tanle Name:     PageHits

    Field 1:        user_id (int)

    Field 2:        page1 (int)

    Field 3:        page2 (int)

    Filed 4:        page3 (int)

    .

    .

    .

    Field10:       page10 (bit).

    If a user had visited page1, then its value = 1, otherwise, it is null. 

    Here is what I wanted:  I'd like to know how many users had visited all 10 pages, and how many visited 9 page, and how many visited 8 pages....

    thanks

  • February 6, 2005 at 1:31 pm

    #540836

    Maybe something like

    select count(*) visitors,  isnull(page1,0) + isnull(page2,0) + isnull(page3,0) + isnull(page4,0) + isnull(page5,0) + isnull(page6,0) + isnull(page7,0) + isnull(page8,0) + isnull(page9,0) + isnull(page10,0) pageCount

    from pagehits group by  isnull(page1,0) + isnull(page2,0) + isnull(page3,0) + isnull(page4,0) + isnull(page5,0) + isnull(page6,0) + isnull(page7,0) + isnull(page8,0) + isnull(page9,0) + isnull(page10,0)

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • February 6, 2005 at 2:35 pm

    #540839

    it works well, thanks

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

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