CODEWITHSUNDEEP
phpmysql
kemu
kemu

Reputation: 33

Display payment made by mark every 3 month for the last 3 years

I have a table called payment it has date field, i have a customer called Mark who has been making payment every day for 3 years 

Table: Payment  
Fields: Name , Amountpaid, date

I want to display payment record made by mark every 3 month and also the total Amountpaid for 3 years

How i want the result to look like

First 3 months payment record table
total Amountpaid at the bottom of the table 

second 3 months payment record table
total Amountpaid at the bottom of the table 

Third 3 months payment record table
total Amountpaid at the bottom of the table

and so on for 3 years

Please do help out

Upvotes: 0

Views: 172

Answers (3)

Caius Jard
Caius Jard

Reputation: 74605

It seems like you're looking for a SQL solution for this, but databases are for holding data, they aren't for formatting it into a report. To this end my advice would be: Don't try and do this in the database, do it in the front end code instead

It will be very simple to run a query like

SELECT * FROM payment WHERE 
  name = 'mark' and 
  `date` between date_sub(now(), interval 3 year) and now()
ORDER BY date

And then put the results into an HTML table usig a loop, and a variable that keeps track of the amount paid total. Every 3 months reset the variable. If you want MySQL to do a bit more data processing to help out you can do this:

SELECT * FROM 
  payment 
  INNER JOIN
  (SELECT YEAR(`date`) + (QUARTER(`date`)/10) as qd, SUM(amountpaid) as qp FROM payment WHERE name = 'mark' GROUP BY YEAR(`date`), QUARTER(`date`)) qpt 
  ON
    qpt.qd = YEAR(`date`) + (QUARTER(`date`)/10)
WHERE 
  name = 'mark' AND
  `date` between date_sub(now(), interval 3 year) and now()
ORDER BY `date`

This will give all mark's data row by row and an extra two columns (that mostly repeat themselves over and over) showing the year and quarter (3 months) of the year like 2017.1, 2017.2, together with a sum of all payments made in that quarter. Formatting it in the front end now won't need a variable to keep a running total of the amount paid

This is about the limit of what you should do with formatting the data in the database (personal opinion). If, however, you're determined to have MySQL do pretty much all this, read on..

Ysth mentioned rollup, which is intended for summarising data.. such a solution would look like this:

SELECT
  Name, `date`, SUM(amountpaid) as amountpaid
FROM
  payment
WHERE 
  name = 'mark' AND
  `date` between date_sub(now(), interval 3 year) and now()
GROUP BY
  name,
  YEAR(`date`) + (QUARTER(`date`)/10),
  `date`
WITH ROLLUP

The only downside with this approach is you also get a totals row for all payments by mark. To suppress that, use grouping sets instead:

SELECT
  Name, `date`, SUM(amountpaid) as amountpaid
FROM
  payment
WHERE 
  name = 'mark' AND
  `date` between date_sub(now(), interval 3 year) and now()
GROUP BY GROUPING SETS 
(
 (
  name,
  YEAR(`date`) + (QUARTER(`date`)/10),
  `date`
 ),
 (
  name,
  YEAR(`date`) + (QUARTER(`date`)/10)
 )
)

Upvotes: 1

Jun Rikson
Jun Rikson

Reputation: 1884

This is for get summary per 3 months :

select year(date)*100+floor(month(date)/3) as period, sum(amountpaid)
from payment
where name = 'mark' and (date between '2014-01-01' and '2017-01-01')
group by year(date)*100+floor(month(date)/3)
order by period

And this is how to get summary 3 year :

select sum(amountpaid) from payment where name = 'mark' and (date between '2014-01-01' and '2017-01-01')

You can change the date between for your need

Upvotes: 0

HostFission
HostFission

Reputation: 384

You can use a group by on the year and month divided by 3 and truncated using floor

SELECT
  EXTRACT(YEAR_MONTH FROM `date`),
  SUM(`Amountpaid`)
FROM
  `Payment`
WHERE
      `Name` = 'Mark'
  AND `date` >= DATE_SUB(NOW(), INTERVAL 3 YEAR)
GROUP BY
  EXTRACT(YEAR FROM `date`),
  FLOOR(EXTRACT(MONTH FROM `date`) / 3)

For the total you will need to iterate the result set and sum up the amounts paid, or if you want it as the final record you could do a UNION SELECT but this would be ineffecient, but for completeness it is below:

SELECT
  EXTRACT(YEAR_MONTH FROM `date`),
  SUM(`Amountpaid`)
FROM
  `Payment`
WHERE
      `Name` = 'Mark'
  AND `date` >= DATE_SUB(NOW(), INTERVAL 3 YEAR)
GROUP BY
  EXTRACT(YEAR FROM `date`),
  FLOOR(EXTRACT(MONTH FROM `date`) / 3)
UNION SELECT
  NULL,
  SUM(`Amountpaid`)
FROM
  `Payment`
WHERE
      `Name` = 'Mark'
  AND `date` >= DATE_SUB(NOW(), INTERVAL 3 YEAR)

Upvotes: 1

Related Questions