SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Hybrid View

  1. #1
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MS SQL: How do i count the empty columns

    hello,
    how do i count the empty columns in a table and assign it to a variable.

    I have a table in my db, that keeps the items a user makes in his selection/subscription for items available.

    PROBLEM;
    1. there are a maximum of 6 options a user can subscribe to.
    2. Minimum of 1

    Once a subscription is made, the options are kept in a table e.g.

    item1
    item2
    item3
    item4
    item5
    item6

    SOLUTION
    Now, if a user subscribes to a maximum of e.g. 3 or 4.
    I would want to be able to count, the number of empty columns and the number of subscriptions and assign it to a variable.

    How do i do this EFFICIENTLY

    thanks
    Afrika

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    do you mean empty or null or both?

    do you want all users or just one?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    do you mean empty or null or both?

    do you want all users or just one?
    Null,
    2nd question; Its to work online with asp, and all users.


    i was trying this code but dont know if its efficient. so i need assistance.

    set @total_items = convert(int,'0')

    if @q1 is not null
    set @total_items = convert(int,'1')
    if @q2 is not null
    set @total_items = convert(int,'2')
    if @q3 is not null
    set @total_items = convert(int,'3')
    if @q4 is not null
    set @total_items = convert(int,'4')
    if @q5 is not null
    set @total_items = convert(int,'5')
    if @q6 is not null
    set @total_items = convert(int,'6')

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select sum(case when item1 is null then 1 else 0 end
              +case when item2 is null then 1 else 0 end
              +case when item3 is null then 1 else 0 end
              +case when item4 is null then 1 else 0 end
              +case when item5 is null then 1 else 0 end
              +case when item6 is null then 1 else 0 end
              ) as grandtotalnulls
      from yourtable
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks a lot Rudy

    Appears you are always there in the nick of time :-)
    Have a wonderful 2005 ahead

    Afrika


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •