I've been trying this for sometime, and appear to have hit a roadblock.

I want to group and count Hours by month.

My SQL Table:

My current attempt:

SELECT FROM_UNIXTIME(Date, '%m-%Y') AS prod_month, COUNT(Hours)
            FROM my_table
            WHERE Date > [last 365 days in unix timestamp]
            AND id = 1
            GROUP BY
            prod_month;"

So, ideally the result would return something like:

CodePudding user response：

Made three tweaks to your query:

• adopted SUM instead of COUNT, to sum up hours
• added ROUND to round your sum to two numbers
• used UNIX_TIMESTAMP to transform current date in unixtime and make comparison in the WHERE clause

SELECT FROM_UNIXTIME(Date_, '%m-%Y') AS prod_month, 
       ROUND(SUM(Hours),2)           AS Hours 
FROM my_table
WHERE UNIX_TIMESTAMP(CURDATE() - INTERVAL 1 YEAR) < Date_
  AND id = 1
GROUP BY FROM_UNIXTIME(Date_, '%m-%Y')

Check the demo here.