MySQL: Select results per category and user for a multiple choice test

Question

I have a multiple choice application with items, options, answers, and user tables:

• An item is basically a question. Each item belongs to one of three categories. The category is stored as a string in the category field.
• Each item has several options linked to it. Each option has item_id and points fields.
• Whenever a user selects an option, an answer is created. It has user_id, item_ id, and option_id fields. There's exactly one answer per item/user pair.

What I need

I'd like to have a query that summarizes the results for each user, one user per row. Each row should contain three columns containing the sum of points the user scored in that category:

Name | Points in Cat1 | Points in Cat2 | Points in Cat3
John | 3              | 1.5            | 2 
Jane | 2              | 2              | 1.5

What I have

I can output points grouped by user and category in separate rows:

SELECT
  u.id,
  u.first_name,
  i.category,
  sum(o.points)

FROM 
  answers a,
  options o,
  items i,
  users u

WHERE a.user_id = u.id
  AND a.option_id = o.id
  AND o.item_id = a.item_id
  AND i.id = a.item_id

GROUP BY u.id, i.category;

# Output:
# John   Cat1   3
# John   Cat2   1.5
# John   Cat3   2
# Jane   Cat1   2
# etc.

I need help modifying the query in the second code listing in a way that gives me the same data, but in a format as I sketched out in the first listing.

Answer 1

use conditional aggregation with case when expression

select firstname,
       max(case when category='Cat1' then p end) "Points in Cat1"
       max(case when category='Cat2' then p end) "Points in Cat2",
       max(case when category='Cat3' then p end) "Points in Cat3"
from
(
SELECT
  u.id,
  u.first_name,
  i.category,
  sum(o.points) as p
FROM 
  answers a join users u on a.user_id = u.id join options o on a.option_id = o.id
  join items i on i.id = a.item_id
GROUP BY u.id, i.category, u.id,u.first_name
)AA group by firstname