Mysql Query Question

[Miriam]

I have an event database named Attendees, which spans a number of years. When attendees sign up, they get a unique auto increment number, so the same attendees can sign up in multiple years. I am trying to get a count of how many of 2004's attendees also attended in 2003. The columns I'm intererested in are the Attendee.LastName and Attendee.EventYear. This is not a large database, so I know the answer I should get.

Total attendees for 2003 and 2004 are 77, 59 in 2003 and 18 in 2004. Of the 18 in 2004, 6 also attended in 2003. So the answer I need to get is 6

 

Here's what I've tried for the query:

 

SELECT COUNT(Attendees.LastName)

FROM Attendees

WHERE Attendees.EventYear='2003' AND Attendees.EventYear='2004'

GROUP BY Attendees.LastName)

 

This doesn't work.

 

Can anyone help me put together a query which will count the duplicate names in the database?

 

Thanks,

Miriam

[btrfld]

Hi Miriam.

The problem may be the AND. Try this:

 

SELECT COUNT(Attendees.LastName)

FROM Attendees

WHERE Attendees.EventYear='2003' OR Attendees.EventYear='2004'

GROUP BY Attendees.LastName

 

Another thing I noticed is the close parenthesis at the end after the GROUP BY clause. Is that a typo in your post or part of the actual query?

 

Hope this helps a little.

Jim

[Miriam]

Using OR wasn't right for what I needed, but someone from another forum provided this answer, and it works. Thought I'd share in case someone else could use it.

 

SELECT COUNT(*)

FROM Attendees

AS A1 INNER JOIN Attendees

AS A2 ON CONCAT(A1.FirstName, A1.LastName) = CONCAT( A2.FirstName, A2.LastName)

WHERE A1.EventYear = '2003' AND A2.EventYear = '2004'

 

I'm still studying it to figure out what it's doing so I can do it again.

 

Miriam

[btrfld]

Of course. I get it. You wanted people who attended both years, not just either or.

 

Thanks for posting the good answer.

 

Jim