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.