Complex SQL query with group by and two rows in one

Question

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...

Answer 1

Create a query/view of the following (say its called PersonLastEvents):

select PersonId, max(EventDate) as LastEventDate
from Events
group by PersonId

Then you can get the data you need with the following. I'm not fully familiar with Access, so you may need to modify some of the syntax, but hopefully this will give you an approach.

select l.PersonId, l.LastEventDate, 
  sum(case when e.SuperCredits = 'false' then e.NumberOfCredits end) 
    as NumberOfNormalCredits
  sum(case when e.SuperCredits = 'true' then e.NumberOfCredits end) 
    as NumberOfSuperCredits
from PersonLastEvents l
join Events e on l.PersonId = e.PersonId and l.LastEventDate = l.EventDate
group by l.PersonId, l.LastEventDate

As an aside, it may be easier to change your table to have two number columns (NormalCredits, SuperCredits) as it allows you to simply sum() the columns as required.

Answer 2

I second ar's suggestion to revise your table structure.

Public Sub createEventsTable()
    Dim strSql As String
    strSql = "CREATE TABLE Events (" & _
        "ID COUNTER CONSTRAINT PK_ID PRIMARY KEY, " & _
        "PersonID INTEGER, " & _
        "EventDate DATETIME, " & _
        "NormalCredits INTEGER DEFAULT 0, " & _
        "SuperCredits INTEGER DEFAULT 0" & _
        ");"
    Debug.Print strSql
    CurrentProject.Connection.Execute strSql
End Sub

With that table structure, you can use this SQL statement to return your desired results:

SELECT
    e1.PersonID,
    e1.LastEventDate,
    Sum(e2.NormalCredits) AS NumberOfNormalCredits,
    Sum(e2.SuperCredits) AS NumberOfSuperCredits
FROM
    (SELECT
        PersonID,
        Max(EventDate) AS LastEventDate
    FROM
        Events
    GROUP BY
        PersonID
    ) AS e1
    INNER JOIN Events AS e2
    ON (e1.LastEventDate = e2.EventDate)
    AND (e1.PersonID = e2.PersonID)
GROUP BY
    e1.PersonID,
    e1.LastEventDate;