MySQL - get sum() of group

Question

I have this table similar to MySQL - get sum() grouped max() of group

id  |  person  |  score  |   date  |   g   |   b   |   m   |
-----------------------------------------------------------
1   |    32    |  444    | 2011-05 |   0   |   1   |   o   |
2   |    65    |  528    | 2011-05 |   1   |   0   |   1   |
3   |    77    |  455    | 2011-05 |   0   |   0   |   1   |
4   |    32    |  266    | 2011-05 |   1   |   1   |   0   |
5   |    77    |  100    | 2011-05 |   0   |   0   |   1   |
6   |    77    |  457    | 2011-05 |   1   |   0   |   0   |
7   |    77    |  457    | 2011-05 |   1   |   0   |   1   |
8   |    65    |  999    | 2011-05 |   0   |   0   |   1   |
9   |    32    |  222    | 2011-05 |   0   |   1   |   1   |

The result should be:

person  |  score  |   date  |   g   |   b   |   m   |
-----------------------------------------------------
  32    |   932   | 2012-05 |   1   |   3   |   1   |
  66    |  1527   | 2012-05 |   1   |   0   |   2   |
  77    |  1469   | 2012-05 |   2   |   0   |   3   |

But I couldn't achieve what I want to. I am trying to get for each person sum of its scores, g, m and b fields in each month. I'm very newbie about subqueries.

Thank you!

do you want the best score AND the sum of scores? – Sebas May 16 '12 at 14:55 — Sebas, May 16 '12 at 14:55
Not the best scores. I only want sum of scores, g, b and m. – Yagiz Gurgul May 16 '12 at 15:26 — Yagiz Gurgul, May 16 '12 at 15:26

D'Arcy Rittich · Accepted Answer · 2012-05-16T15:08:35.417

1

You can't just group by month, since the same month can appear in more than one year, so group by year as well, e.g.:

SELECT person, YEAR(`date`), MONTH(`date`), 
    SUM(score), SUM(b), SUM(g), SUM(m)
FROM MyTable
GROUP BY person, YEAR(`date`), MONTH(`date`)

If your date column is actually not a date type but a varchar or similar, and it contains yyyy-dd as shown, than you can just do:

SELECT person, `date`, 
    SUM(score), SUM(b), SUM(g), SUM(m)
FROM MyTable
GROUP BY person, `date`

edited May 16 '12 at 15:08

answered May 16 '12 at 14:55

D'Arcy Rittich

167,292
40
290
283

I edited my question. I also need to sum g,b,m fields. I think adding SUM(g), SUM(b) and SUM(m) should work? – Yagiz Gurgul May 16 '12 at 15:03
Yes, that's all there is to it. – D'Arcy Rittich May 16 '12 at 15:07
I get only the first row of each persons. With SUM(score) score, SUM(g) g, SUM(b) b, SUM(m) m I got what I want. Thank you! – Yagiz Gurgul May 16 '12 at 15:29

MySQL - get sum() of group

1 Answers1