SQL query to group by month part of timestamp

问题:

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

Here is my current query:

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

My table looks like:

#########################################
# 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         #
#########################################

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.

Thanks

回答1:

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

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.