Aggregating statistics from child rows

I have a parent row and then many child rows, and I must aggregate statistics from these child rows.

For a more concrete example let’s imagine I have one round of golf (parent row) and then child rows with the score of each hole. I need to aggregate statistics on these rows, like number of birdies, pars, bogies, putts and so forth. And then put these numbers in a round_detail table.

What would be the best solution to this?

I’m using postgres if it matters, but this is probably just a general sql propblem.

EDIT: As requested http://sqlfiddle.com/#!1/6549c

The concept you consider is known as materialized view. Since you comment:

rounds are rarely edited afterwards

.. I think this should be good idea for your case. Some RDBMS’ have distinct objects for that. In Postgres it’s just a matter of writing the results from a view or SRF (set returning function) to a table. You can find a basic code example for refreshing MVs in the manual here.

Refer to this related answer on SO for the more sophisticated regime I use for that purpose.

Your query to (re-)create a materialized view could look something like this:

SELECT c.name AS course
      ,r.user_id
      ,s.round_id
      ,COUNT(NULLIF(s.strokes = h.par, FALSE))     AS pars
      ,COUNT(NULLIF(s.strokes = h.par - 1, FALSE)) AS birdies
      -- more columns ..
FROM   score  s
JOIN   round  r ON r.id = s.round_id
JOIN   course c ON c.id = r.course_id
JOIN   hole   h ON h.id = s.hole_id
GROUP  BY 1,2,3

I simplified your test case and added the query: ->sqlfiddle.