Get price by month in mysql-CodePudding

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

CodePudding user response：

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;

CodePudding user response：

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

CodePudding user response：

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');