mysql 平均月份,Mysql得到平均值和列的总和，并按年份&月

I have a query wherein I need to return the average of the price and the sum of the qty and group the results by year and month. This is the start of my query, just don't know how to get the results that I need.

SELECT asin,

price,

qtyTotal,

qtyReserved,

qtyWarehouse,

qtyFulfillable,

qtyUnsellable,

perUnitVolume,

YEAR(reportDate),

MONTH(reportDate),

DAY(reportDate)

FROM Table

WHERE name = 'XXXXXXX'

ORDER BY reportDate ASC

id | name | price | qty | unitVol | year | month | day | reportDate

---|-----------------------------------------------------------------------------

1 | XXXXXXX | 20.18 | 3 | 0.17 | 2014 | 8 | 23 | 2014-8-23

2 | XXXXXXX | 20.19 | 3 | 0.18 | 2015 | 11 | 10 | 2014-8-23

3 | XXXXXXX | 20.21 | 3 | 0.19 | 2015 | 11 | 11 | 2014-8-23

4 | XXXXXXX | 20.22 | 3 | 0.20 | 2015 | 11 | 12 | 2014-8-23

5 | XXXXXXX | 20.43 | 3 | 0.11 | 2015 | 12 | 1 | 2014-8-23

6 | XXXXXXX | 23.34 | 3 | 0.13 | 2015 | 12 | 2 | 2014-8-23

7 | XXXXXXX | 25.54 | 3 | 0.19 | 2015 | 12 | 3 | 2014-8-23

This is the result I need to end up with:

id | name | price | qty | unitVol | year | month

---|------------------------------------------------------------------

1 | XXXXXXX | 20.18 | 3 | 0.17 | 2014 | 8

2 | XXXXXXX | 20.21 | 9 | 0.19 | 2015 | 11

3 | XXXXXXX | 23.10 | 9 | 0.14 | 2015 | 12

The price is the average of the price for each record with the same year and month, for instance record id #2 is: (20.19 + 20.21 + 20.22) / 3 = 20.21

The qty is the sum of the year and month records, for instance record id #2 is: 3 + 3 + 3 = 9

Thanks for your help.

解决方案SELECT id, name, AVG(price), SUM(qty), AVG(unitVol), year, month

FROM Table

WHERE name = 'XXXXXXX'

GROUP BY year, month