Search code examples
mysqlchecksum

MySql Query for Summarizing per Year or per Month

I have the following table structure: table={id,DailyDate,value1,value2} with DailyDate ex. 2000-01-01

How i can get a summarize result firstly per month and secondly per year for each value?

I was trying the following but it doesn't work

Select Sum(Value1),Year(DailyDate),Month(DailyDate)
From table
Group by Year(DailyDate),Month(DailyDate)
Order by Year(DailyDate),Month(DailyDate)

ty

Solution

  • If what you're asking for is subtotals by year, try this:

    SELECT TheSum, TheYear, COALESCE(TheMonth, CONCAT('Total Year ', TheYear))
FROM (
  SELECT
    SUM(Value1) TheSum,
    YEAR(DailyDate) AS TheYear,
    MONTH(DailyDate) AS TheMonth
  FROM table
  GROUP BY Year(DailyDate), MONTH(DailyDate) WITH ROLLUP) x
WHERE TheYear IS NOT NULL

    The inner query uses WITH ROLLUP to generate subtotals by month and a grand total by year. The outer query formats the year and drops the grand total.