Get price by month in mysql

byMR

September 26, 2022

I need to get the total price per month.

I have

inited_time (ex. 20160530105130)
first_payment_price
deleted

I tried grouping by inited_time

SELECT inited_time, DATE_FORMAT(inited_time, '%c') as month, SUM(first_payment_price) as price
FROM some_table
WHERE inited_time > 0 AND deleted = 0
GROUP BY inited_time

But it doesn’t sum them together.
ex of result:

[
  {
    "inited_time": 20160530105130,
    "month": "5",
    "price": 25000.00
  },
  {
    "inited_time": 20160530105157,
    "month": "5",
    "price": 100000.00
  },
  {
    "inited_time": 20160610000002,
    "month": "6",
    "price": 75000.00
  },
  {
    "inited_time": 20160617000001,
    "month": "6",
    "price": 50000.00
  },
  {
    "inited_time": 20160701000001,
    "month": "7",
    "price": 80000.00
  },
  {
    "inited_time": 20160702000001,
    "month": "7",
    "price": 200000.00
  }
]

>Solution :

Ideally the inited_time column would be a format date or timestamp. Assuming it is fixed width, we can aggregate by month using division and modulus on this column to isolate the month number:

SELECT FLOOR(inited_time / 100000000) % 100 AS month, SUM(first_payment_price) AS price
FROM some_table
WHERE inited_time > 0 AND deleted = 0
GROUP BY 1;

Assuming you might have multiple years in your data set and you would want to report each year month separately, we can try using STR_TO_DATE followed by DATE_FORMAT:

SELECT DATE_FORMAT(STR_TO_DATE(inited_time, '%Y%m%d%H%i%s'), '%Y-%m') AS ym,
       SUM(first_payment_price) AS price
FROM some_table
WHERE inited_time > 0 AND deleted = 0
GROUP BY 1
ORDER BY 1;