SQL查询按时间戳的月份部分分组 [英] SQL query to group by month part of timestamp

问题描述

我在 SQL 查询方面真的很糟糕，但我正在学习，所以请原谅这个问题.

I'm really bad at SQL queries but I'm learning so please forgive this question.

这是我当前的查询:

SELECT TIMESTAMP, SUM( electricity ) AS electricity,  `siteID` 
FROM table
WHERE (
MONTH(  `TimeStamp` ) =10)
GROUP BY siteID

我的桌子是这样的:

#########################################
# Id # SiteID # TimeStamp  # Elecricity #
# 0  # 100    # 10/08/2012 # 50         #
# 1  # 98     # 10/08/2012 # 32         #
# 2  # 100    # 10/09/2012 # 96         #
# 3  # 94     # 10/09/2012 # 25         #
# 4  # 100    # 10/10/2012 # 100        #
# 5  # 100    # 10/11/2012 # 55         #
#########################################

我要做的是总结每个站点每个月的所有天数，以便每个站点和每个月都有一个答案.因此，在本例中，查询应返回 siteID 100 的电量值的总和，因为它们都在第 10 个月，对于 siteID 98 和 94 也是如此.

What I am trying to do is to sum up all of the days of each month of each site, so that I will have one answer per site and month. So in this example the query should return the sum of the electricity values for siteID 100 because they're all in month 10, the same for siteID 98 and 94.

谢谢

推荐答案

SELECT month('TIMESTAMP'), SUM( electricity ) AS electricity,  `siteID` 
FROM table
WHERE (
MONTH(  `TimeStamp` ) =10)
GROUP BY siteID, month('TIMESTAMP')

这会奏效.你必须考虑的一件事是那个月不是唯一的.在这种情况下，2012 年 10 月与 2013 年 10 月相同.您可能需要为年份添加另一列.

This will work. One thing that you have to think about is that month is not unique. Oct, 2012, in this case, is the same as Oct 2013. You might want to add another column for year.

这篇关于SQL查询按时间戳的月份部分分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！