MySQL query to fetch report month wise

Databases
1

I have the table design the same as below, I am trying to fetch the column money value of all months that comes in the year.
suppose I post the year 2023 then the report will show the all months with money value, from January to December.

I am near the goal but thereafter I don’t know how I do this, if anybody has an idea, please help me, with how I make the query for MySQL, phpmyadmin.

ID | period       |  money  | user
----------------------------------------------
1  | 2023-01      | 5       | usr1
----------------------------------------------
2  | 2023-02      | 6      | usr1
----------------------------------------------
3  | 2023-03      | 7      | usr1
----------------------------------------------
4  | 2023-04      | 8      | usr1

SELECT
  ID,
  MAX(CASE WHEN MONTH(period) = 1 THEN money END) AS january,
  MAX(CASE WHEN MONTH(period) = 2 THEN money END) AS february,
  MAX(CASE WHEN MONTH(period) = 3 THEN money END) AS march,
  MAX(CASE WHEN MONTH(period) = 4 THEN money END) AS april,
  MAX(CASE WHEN MONTH(period) = 5 THEN money END) AS may
FROM
  table
WHERE
  YEAR(period) = 2023
GROUP BY
  ID;
2 
SELECT MONTHNAME(PERIOD) AS PeriodMonth, MAX(Money) AS MaxValue
  FROM table
 WHERE YEAR(Period) = 2023
GROUP BY MONTHNAME(PERIOD)

should return you something like

PeriodMonth   | MaxValue
-----------------------------------
January       | 5
February      | 6
March         | 7
April         | 8

Though are you looking for MAX (highest amount) or SUM (total amount) for each month?

2 Likes
3

it returns nothing

the type of period column is “varchar(255)”

4

What do you get from this?

SELECT YEAR(Period) from table

Does it give what you expect?

5

That’s an important little piece of information.

What format is the data actually in? If it’s in mm-yyyy format, then the query would have to be changed to something like this which converts the string to a date format before MonthName can be run.

SELECT MONTHNAME(CONCAT(SUBSTRING(period, 4, 4), "-", SUBSTRING(period, 1, 2), "-01")) as Period 
     , MAX(Money) AS MaxValue
  FROM table
 WHERE CONCAT(SUBSTRING(period, 4, 4) = '2023'
GROUP BY MONTHNAME(CONCAT(SUBSTRING(period, 4, 4), "-", SUBSTRING(period, 1, 2), "-01"))
6

looks like %Y-%m

WITH mydata AS
     ( SELECT STR_TO_DATE(CONCAT(period,'-01')
                         , '%Y-%m-%d') AS period_date 
            , Money
         FROM table )
SELECT MONTHNAME(period_date) AS period
     , MAX(Money) AS max_value
  FROM mydata
 WHERE YEAR(period_date) = 2023
GROUP 
    BY MONTHNAME(period_date)
1 Like
7

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.