Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2009
    Posts
    50

    Unanswered: need all rows where less than 50% of columns are true

    I have a table that consits of 63 bool columns. I would like to know if there is a way to query only rows where less than 50% of the columns are true?

    Code:
    Select *
    From Skillset
    Where (Select COUNT(true)/ Select COUNT(*) )*100 < 50%
    I know that wont work, but thats what I'm searching for. I need to report on any employee that is below 50% on their skill set certifications.
    Can anyone help with this?

    Thanks
    Jim

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT emp_id
         , result
      FROM ( SELECT emp_id
                  , CASE WHEN column1 IS TRUE THEN 1 ELSE 0 END +
                    CASE WHEN column2 IS TRUE THEN 1 ELSE 0 END +
                    CASE WHEN column3 IS TRUE THEN 1 ELSE 0 END +
                    ...
                    CASE WHEN column63 IS TRUE THEN 1 ELSE 0 END AS result
              FROM Skillset ) AS d
     WHERE result < 32
    if you find that this is awkward, realize that the 63 columns violate first normal form

    you would be able to use the COUNT approach only if the table were properly normalized
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2009
    Posts
    50
    Sorry, I'm a hobby developer that learned enough SQL to be dangerous.
    What is "First Normal Form ? And how can I redisign this table to work more efficiently?

  4. #4
    Join Date
    Dec 2009
    Posts
    50
    So I looked it up and have an idea of what FN1 is now. However, I'm not sure what your meaning is. There will only be one row per employee and each row will have all 63 columns. There is no repeating. Can you please clarify what you mean?

    Thanks
    Jim

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jbedson View Post
    Can you please clarify what you mean?
    the boolean columns repeat

    which means you have to go across the row, counting them with CASE expressions

    if you had a row for each boolean, you could count them with the COUNT aggregate function
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Dec 2009
    Posts
    50
    ok. Thank you for the assistance.
    Jim

  7. #7
    Join Date
    Dec 2009
    Posts
    50
    Sorry for the trouble, but I'm using Access and CASE does not work. There is switch statement that is meant to do the same thing. Not sure if you can help w/ Access of if this is the correct forum for it ( I looked around and did not see anything specific to Access). But if you are able to help I would greatly appriciate it. Cant figure out how to get switch to work the same.

    Thanks again
    Jim

  8. #8
    Join Date
    Dec 2009
    Posts
    50
    Thanks r937. I found the Access forum and have posted the question there.
    I appreciate the assistance
    Jim

Posting Permissions

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