Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2012
    Posts
    4

    COUNT Help, Should be fairly simply.

    Hello,

    Scenario:
    I'm building a small database for a school to help maintain their Music Lessons. These lessons take place over 3 days but all the different lessons happen at once on each day. So a member can only be down for 1 Session "day". Though its possible on the table at the moment for a member to be saved for 2+ lessons which take place on the same day.

    Query:
    I'm in need of having a Query where I can see if members have been assigned to the same Session more than once.

    I'm fairly new to SQL, and I'm having to use MS ACCESS due to the School but it shouldn't matter.

    I feel the best way Would be to have a query for each Session, and for it to just read out the names of who are down for more than 1 of that Session.


    The names etc. of all the members are saved on one table and there is a second table which matches the member to a Lesson and states the Session.

    Last Feeble Attempt:
    I've tried many ways I feel could work, but to no avail, this is my last try I'm getting tired and frustrated so this is probably retarded.

    SELECT [Member Workshops].Member
    FROM [Member Workshops]
    HAVING (COUNT(Session = Session 2) > 2);

    This is all for Work Experience placement from Uni and have been taught very little when it comes to even simple mixing of Functions. So apologies if I seem very knowledgeable, because I am.

    P.S: I made the "Member" and "Session" both primary keys in the table I linked above which makes what this whole query wants to check impossible to happen, but I'm being told to do it any way -.-'

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT Member
         , Session
      FROM [Member Workshops]
    GROUP
        BY Member 
         , Session
    HAVING COUNT(*) > 2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2012
    Posts
    4
    Thanks!

    This is just what I needed plus will work for all sessions in one query,

    Though can I ask how the COUNT(*) function knows I want the Session to be counted, as members appears multiple times also.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the COUNT(*) function counts the number of sessions per member

    it knows what to count via the GROUP BY clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,566
    Quote Originally Posted by Veréonix View Post
    P.S: I made the "Member" and "Session" both primary keys in the table I linked above which makes what this whole query wants to check impossible to happen, but I'm being told to do it any way -.-'
    So what fool has ordered you to write a query to verify that SQL Server hasn't violated an enforced constraint?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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