Question 113492 in Mysql Question posted in
The official MySQL documentation can be found here.

Get price by month in mysql

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
  }
]
Tags:

3

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    I need to get the total price per month.

    Simply, group by month and remove inited_time from the select clause

    SELECT  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 DATE_FORMAT(inited_time, '%c');

    Edit. If you have different years included add another condition DATE_FORMAT(20160530105130, '%Y'), or if you want only one year even though you have different years filter the needed year on the where clause. For example for 2016 year the condition would be DATE_FORMAT(inited_time, '%Y') = 2016

    SELECT  DATE_FORMAT(inited_time, '%c') as month,
        DATE_FORMAT(inited_time, '%Y') as year,
        SUM(first_payment_price) as price
FROM   some_table
WHERE inited_time > 0 
AND deleted = 0
GROUP BY DATE_FORMAT(inited_time, '%c'),DATE_FORMAT(inited_time, '%Y');
    Login or Signup to reply.
  3. 0

    group by month

    SELECT  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 month
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search