i have a MYSQL database where i want to sort the totals of both paind and unpaid amounts. The Query i used was :
SELECT DISTINCT
YEAR( app_payments.created_at ) AS YEARS,
SUM( app_payments.amount ) AS Total,
app_users.serial,
app_payments.`status` AS payment_state
FROM
app_payments
INNER JOIN app_users ON app_payments.created_by = app_users.serial
WHERE
app_payments.created_by = 'd88faa'
GROUP BY
YEAR ( app_payments.created_at ),
app_payments.status
i got the results as:
2017 1995 d88faa 1
2018 1200 d88faa 1
2019 1250 d88faa 0
2019 4990 d88faa 1
Where 1
stands for PAID
and 0
stand for UNPAID
in my php code, i tried to group the data into years
$Stats = array ();
while(!$this->EndofSeek()){
$result = $this->Row();
if($result->payment_state == 0 ){
if(in_array($result->YEARS,$Stats)){
array_replace($Stats,['y'=>$result->YEARS , 'b'=>$result->Total ]);
}else{ array_push($Stats,['y'=>$result->YEARS , 'a'=>0 , 'b'=>$result->Total ]);}
}else if($result->payment_state == 1){
array_push($Stats,['y'=>$result->YEARS , 'a'=>$result->Total , 'b'=>0 ]);
}
}
return json_encode($Stats)
This returns the output:
[{"y":"2017","a":"1995","b":0},
{"y":"2018","a":"1200","b":0},
{"y":"2019","a":"4990","b":"1450"},
{"y":"2019","a":"4990","b":0}]
Where y
is YEARS
, a
is PAID
and b
is UNPAID
What i seek to achieve is to group all the data to a particular year where i would have
[{"y":"2017","a":"1995","b":0},
{"y":"2018","a":"1200","b":0},
{"y":"2019","a":"4990","b":"1450"}]
Without it duplicating the year but rather merging them into a single unit.
What do i need to do, and which code do i need to implement to achieve this.