Aggregate, sum and pivot MySQL data

128 views Asked by At

I have the following mysql query:

SELECT sub_nine, sum(sub.sub_low_nett) AS points, sub_pid FROM
  (SELECT c.cn_nine as sub_nine, min(rh.rh_nett) AS sub_low_nett, p.id AS sub_pid
    FROM round r, round_hole rh, player p, course_nine c, course_hole ch
    WHERE r.r_id = rh.rh_rid
    AND p.id = r.r_pid
    AND c.cn_nine = r.r_nine
    AND ch.ch_nine = c.cn_nine
    AND rh.rh_hid = ch.ch_no
    GROUP BY p.id, c.cn_nine, ch.ch_no
  ) AS sub
GROUP BY sub_pid, sub_nine
ORDER BY sub_pid

Which returns the following result:

 |sub_nine|points|sub_pid
 |   1    |  2   |   1
 |   2    |  17  |   1
 |   3    | NULL |   1
 |   1    | NULL |   2
 |   2    | NULL |   2
 |   3    | NULL |   2
 |   1    | NULL |   3
 |   2    |  26  |   3
 |   3    |  4   |   3
 |   1    |  26  |   5
 |   2    |  36  |   5

The result set has been looped into a multi-dimensional array, using the fetch_assoc() function.

I need to display each of the points totals per player as well as the total of those points (distinctly / uniquely) for each player in a table

I think I need to loop through the initial multi-dimensional array, and probably place into a new array that has been pivoted around the columns rather than rows, but stuck as to how to do this.

The initial query is sorted by pid, so I know that as you loop through the array, the rows can be cumulatively totaled.

I also need the final array to be sorted by the new total value per player as well.

pid | score1 | score2| score3 | total

the data structure is laid out here: Gold DB ERD Diagram

0

There are 0 answers