How can I make an Aging Report using mysql? I have a table called supplier 

CREATE TABLE supplier_invoices (
id INT PRIMARY KEY AUTO_INCREMENT, 
supplier_id INT NOT NULL,  
date DATE NOT NULL, 
invoice_no INT NOT NULL, 
dr_amount DOUBLE NOT NULL,
cr_amount DOUBLE NOT NULL, 
balance DOUBLE NOT NULL);


and it contains the above fields. I want to create an aging report basing on the data in that table. With difference of days between 0-30days, 31-60days, 61-90days, 90 and above days. 

How can I achieve this. Am using mysql.
The CASE statement will work. It returns a value depending on a condition, so you can assign each invoice's value to exactly one age bucket.

To be able to roll up the values at whatever granularity required use a sub-query to put values into appropriate buckets, then in the super-query perform the required grouping.

select
  sum(Balance30) as Balance30,
  sum(Balance60) as Balance60,
  ..etc.
from (
  select
    supplier_id,
    case
      when datediff (curdate (), order.date) &lt;= 30 then order.balance
      else 0
    end as Balance30,
    case
      When datediff (curdate (), order.date) &gt; 30 
        and datediff (curdate (), order.date) &lt;= 60 then order.balance
      Else 0
    End as Balance60,
  ... etc
) as SomeAlias
group by
  supplier_id,
  .. or whatever


DATEDIFF calculates how old each invoice is, according to your chosen reference date. The CASE decides which bucket it should be in, and returns zero for each row which is not in this bucket. This assigns each invoice's value into exactly one of the buckets. The output from the inner query is one row per row in the base table, with key values and one column per bucket. The SUM in the outer query tallies all rows according to the grouping conditions, but we've substituted zero for non-matching rows so effectively filtered them out.

This assumes you want a column per bucket. If you need a row per bucket the same technique applies. Instead of returning order.balance from the CASE, return a bucket name. Wrap the whole query in group by bucketName and you will have the desired outcome.