Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2012
    Posts
    3

    Unanswered: Show records based on value

    Hi everyone,

    I'm going to get straight to it. Basically, what I need to do is show records based on another field.

    The scenario:

    I have a list of employees, the employees have attended many courses (there are different courses). The employees have either passed or failed the course. I need to create a report that shows only those that have failed 3 or more courses. The calculation has to be based on a yes/no field. I am guessing that a query has to be made, however I don't know how to go about it .

    Any ideas?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    start by showing us a query that retrieves all students and all their courses
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Query to show number of fails for each person [that has failed a test]
    Code:
    SELECT person
         , Count(*)
    FROM   courses
    WHERE  pass = 0
    GROUP
        BY person
    To limit this query to show only those who have 3 or more failures add this to the end
    Code:
    HAVING Count(*) >= 3
    George
    Home | Blog

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    george, come on, you have to make it just a ~wee~ bit harder for these n00bs to get their homework assignments completed
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2012
    Posts
    3
    This works, thank-you, however for the last part, I get the error 'Characters found after end of statement.

    Thanks again


    Quote Originally Posted by gvee View Post
    Query to show number of fails for each person [that has failed a test]
    Code:
    SELECT person
         , Count(*)
    FROM   courses
    WHERE  pass = 0
    GROUP
        BY person
    To limit this query to show only those who have 3 or more failures add this to the end
    Code:
    HAVING Count(*) >= 3

  6. #6
    Join Date
    Apr 2012
    Posts
    3

    Thank youuuuu

    No worries, found that I forgot to remove the ';'

    Thank you soo much

Tags for this Thread

Posting Permissions

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