If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > COUNT Help, Should be fairly simply.

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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 -.-'
Reply With Quote
  #2 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
Code:
SELECT Member
     , Session
  FROM [Member Workshops]
GROUP
    BY Member 
     , Session
HAVING COUNT(*) > 2
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #4 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
the COUNT(*) function counts the number of sessions per member

it knows what to count via the GROUP BY clause
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #5 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,521
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On