CODEWITHSUNDEEP
mysqldatabasegroup-by
Den Pat
Den Pat

Reputation: 1254

Mysql Group by specific time intervals

Is there anyway to group by specific time intervals for example, i have this type of table :

invoice_id  due_date    invoice_balance
4           2019-02-04  192
5           2019-03-18  0   
6           2019-03-18  3
7           2019-04-24  30392   
8           2019-04-25  47.5

Now i have many such records and i want them to group by specific date intervals ( today , today+7 day , 7dayto30day, morethan30days ) with sum of amount total :

Expected Results :

total  date between
2000   today
2000   today+7 day
2000   7dayto30day
2000   morethan30days

so i tried something like this :

SELECT SUM(invoice_balance) as sum,invoice_id, due_date 
FROM `my_invoice_master` 
GROUP BY if(due_date between "2019-02-04" and "2019-04-26",0,1),if(due_date between "2019-07-05" and "2019-07-09",0,1) 
ORDER BY u.meta_value asc LIMIT 0 , 10

But it doesn't work, is there anyway to return result with those specified date intervals ?

Upvotes: 0

Views: 51

Answers (2)

forpas
forpas

Reputation: 164064

You can do it with conditional aggregation:

SELECT 
  SUM(case 
        when due_date = current_date then invoice_balance 
        else 0 
      end) as `today`,
  SUM(case 
        when due_date between current_date + interval 1 day and current_date + interval 7 day then invoice_balance 
        else 0 
      end) as `today+7 day`,
  SUM(case 
        when due_date between current_date + interval 8 day and current_date + interval 30 day then invoice_balance 
        else 0 
      end) as `7dayto30day`,  
  SUM(case 
        when due_date > current_date + interval 30 day then invoice_balance 
        else 0 
      end) as `morethan30days`
FROM `my_invoice_master`

Upvotes: 1

Yuliya Narbut
Yuliya Narbut

Reputation: 81

You can use function IF:

select invoice_id, invoice_balance, 
  IF(DATEDIFF(DATE(due_date), DATE(NOW()))<=7
    , IF(DATEDIFF(DATE(due_date), DATE(NOW()))>0, 'today+7', 'today')
    , IF(DATEDIFF(DATE(due_date), DATE(NOW()))<=30, '7dayto30day', 'morethan30days')
  ) as due_period
from my_invoice_master

This query show you all records by periods. If due_date will be less then today it gets you 'today'.

If you need sum invoice_balance by invoice_id use this:

select invoice_id, sum(invoice_balance), 
  @due_period:= IF(DATEDIFF(DATE(due_date), DATE(NOW()))<=7
    , IF(DATEDIFF(DATE(due_date), DATE(NOW()))>0, 'today+7', 'today')
    , IF(DATEDIFF(DATE(due_date), DATE(NOW()))<=30, '7dayto30day', 'morethan30days')
  ) as due_period
from my_invoice_master
group by invoice_id, @due_period

If you need sum invoice_balance by period use this:

select sum(invoice_balance), 
  @due_period:= IF(DATEDIFF(DATE(due_date), DATE(NOW()))<=7
    , IF(DATEDIFF(DATE(due_date), DATE(NOW()))>0, 'today+7', 'today')
    , IF(DATEDIFF(DATE(due_date), DATE(NOW()))<=30, '7dayto30day', 'morethan30days')
  ) as due_period
from my_invoice_master
group by @due_period

Upvotes: 0

Related Questions