title: How to group data by year with paid and unpaid values
body: <p>i have a  MYSQL database where i want to sort the totals of both paind and unpaid amounts. The Query i used was :</p>
  
  <pre><code>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 
  </code></pre>
  
  <p>i got the results as:</p>
  
  <pre><code>2017    1995    d88faa  1
  2018    1200    d88faa  1
  2019    1250    d88faa  0
  2019    4990    d88faa  1
  </code></pre>
  
  <p>Where <code>1</code> stands for <code>PAID</code> and <code>0</code> stand for <code>UNPAID</code></p>
  
  <p>in my php code, i tried to group the data into years </p>
  
  <pre><code>$Stats = array ();
  while(!$this-&gt;EndofSeek()){
  $result = $this-&gt;Row();
  if($result-&gt;payment_state == 0 ){
   if(in_array($result-&gt;YEARS,$Stats)){
   array_replace($Stats,['y'=&gt;$result-&gt;YEARS , 'b'=&gt;$result-&gt;Total ]);
  }else{ array_push($Stats,['y'=&gt;$result-&gt;YEARS , 'a'=&gt;0 , 'b'=&gt;$result-&gt;Total ]);}
  }else if($result-&gt;payment_state == 1){
   array_push($Stats,['y'=&gt;$result-&gt;YEARS , 'a'=&gt;$result-&gt;Total , 'b'=&gt;0 ]);
  }
   }
    return json_encode($Stats)
  </code></pre>
  
  <p>This returns the output:</p>
  
  <pre><code>[{"y":"2017","a":"1995","b":0},
  {"y":"2018","a":"1200","b":0},
  {"y":"2019","a":"4990","b":"1450"},
  {"y":"2019","a":"4990","b":0}]
  </code></pre>
  
  <p>Where <code>y</code> is <code>YEARS</code> , <code>a</code> is <code>PAID</code> and <code>b</code> is <code>UNPAID</code></p>
  
  <p>What i seek to achieve is to group all the data to a particular year where i would have
      <code>[{"y":"2017","a":"1995","b":0},
      {"y":"2018","a":"1200","b":0},
      {"y":"2019","a":"4990","b":"1450"}]</code></p>
  
  <p>Without it duplicating the year but rather merging them into a single unit.</p>
  
  <p>What do i need to do, and which code do i need to implement to achieve this.</p>