Mysql得到平均值和列的总和，并按年份&月 [英] Mysql get average and sum of columns and group by year & month

问题描述

我有一个查询，我需要返回价格的平均值和数量的总和，并将结果按年和月分组。这是我查询的开始，只是不知道如何获得我需要的结果。

  SELECT asin，
价格，
 qtyTotal，
 qtyReserved，
 qtyWarehouse ，
 qtyFulfillable，
 qtyUnsellable，
 perUnitVolume，
 YEAR（reportDate），
 MONTH（reportDate），
 DAY（reportDate）
 FROM表
 WHERE name ='XXXXXXX'
 ORDER BY reportDate ASC 
 
 
 id |名称|价格| qty | unitVol |年| |月|一天| 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 
  

这是我需要得到的结果：

  id |名称|价格| qty | unitVol |年| |月
 --- | ----------------------------------------- ------------------------- 
 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 
  

例如，价格是同一年份和月份的每个记录的平均价格记录ID＃2是：（20.19 + 20.21 + 20.22）/ 3 = 20.21

数量是年份和月份记录的总和，例如记录ID＃2是：3 + 3 + 3 = 9

感谢您的帮助。

解决方案

  SELECT id，name，AVG（price） （unitVol），year，month 
 FROM Table 
 WHERE name ='XXXXXXX'
 GROUP BY年，月
  

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

这篇关于Mysql得到平均值和列的总和，并按年份&月的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！