0

I'm trying to do almost same thing as this question but I can't seem to generate my objective after I tried to alter that to my case.

   id, type, time, amount
   ------------
   18, depo, 2020-02-02 10:13:00, 30
   17, full, 2020-02-02 10:12:00, 100
   16, full, 2020-02-01 15:12:00, 100
   15, full, 2020-02-01 13:12:00, 100

All I need is to SUM() all group by date, I've done this.

SELECT sum(amount) amount, DATE(time) day 
  FROM `payment` 
 GROUP BY DATE(time), type 
 ORDER BY day, type DESC

It returns two same dates 

   70, 2020-02-02
   100, 2020-02-02
   200, 2020-02-01

The operation I need is full minus by (-) depo so on 2020-02-02 I will get 70. Same with other dates. I'm thinking to assign all the data to PHP arrays butt is there an "easier" workaround so I could just call the data straight from the query.

Thanks in advance.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Iqbal
  • 73
  • 1
  • 8
  • @danish-khan-I yes I need the amount for 'full' type minus by 'depo' type for same date. Unnormalized ik. – Iqbal Feb 02 '20 at 08:40

2 Answers2

1

You can use conditional (case..when clause) within aggregation :

SELECT sum(case when type = 'depo' then - amount else amount end) amount, DATE(time) day 
  FROM `payment` 
 GROUP BY DATE(time), type 
 ORDER BY day, type DESC
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
0

You are getting two lines because of the group by clause : group by type : one line for depo and one line for full, if the only lines you need to aggregate are lines where type in ('full', 'depo'), then, you can just group by Date (I based my answer on @Barbaros-Özhan answer)

SELECT sum(case when type = 'depo' then - amount else amount end) amount, DATE(time) day 
  FROM `payment` 
  WHERE type IN ('full', 'depo')
 GROUP BY day 
 ORDER BY day

I have also removed the ORDER BY type, since you said all you need is SUM by DATE.

Demo

hsibboni
  • 433
  • 2
  • 8