Complex SQL query with group by and two rows in one
- by Ricket
Okay, I need help. I'm usually pretty good at SQL queries but this one baffles me. By the way, this is not a homework assignment, it's a real situation in an Access database and I've written the requirements below myself.
Here is my table layout. It's in Access 2007 if that matters; I'm writing the query using SQL.
Id (primary key)
PersonID (foreign key)
EventDate
NumberOfCredits
SuperCredits (boolean)
There are events that people go to. They can earn normal credits, or super credits, or both at one event. The SuperCredits column is true if the row represents a number of super credits earned at the event, or false if it represents normal credits.
So for example, if there is an event which person 174 attends, and they earn 3 normal credits and 1 super credit at the event, the following two rows would be added to the table:
ID PersonID EventDate NumberOfCredits SuperCredits
1 174 1/1/2010 3 false
2 174 1/1/2010 1 true
It is also possible that the person could have done two separate things at the event, so there might be more than two columns for one event, and it might look like this:
ID PersonID EventDate NumberOfCredits SuperCredits
1 174 1/1/2010 1 false
2 174 1/1/2010 2 false
3 174 1/1/2010 1 true
Now we want to print out a report. Here will be the columns of the report:
PersonID
LastEventDate
NumberOfNormalCredits
NumberOfSuperCredits
The report will have one row per person. The row will show the latest event that the person attended, and the normal and super credits that the person earned at that event.
What I am asking of you is to write, or help me write, the SQL query to SELECT the data and GROUP BY and SUM() and whatnot. Or, let me know if this is for some reason not possible, and how to organize my data to make it possible.
This is extremely confusing and I understand if you do not take the time to puzzle through it. I've tried to simplify it as much as possible, but definitely ask any questions if you give it a shot and need clarification. I'll be trying to figure it out but I'm having a real hard time with it, this is grouping beyond my experience...
