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