I have a question. So I have this data in array :
id idm amount date
1 5 10 2017-08-23 12:12:12
2 5 20 2017-08-23 12:14:16
3 6 13 2017-08-23 18:00:00
4 5 25 2017-08-24 19:00:00
5 5 160 2017-08-24 19:30:00
So the idea is to get the sum of amount from date 2017-08-23 and compare with date 2017-08-24. If difference between those 2 values for a user is bigger than 20 for example in this case I found a user.
My propose was to make 2 sql's :
select sum(amount) as previous_amount, idm
FROM table
WHERE date >= '2017-08-23 00:00:00' AND date <= '2017-08-23 23:59:59'
GROUP By idm
select sum(amount) as actual_amount, idm
FROM table
WHERE date >= '2017-08-24 00:00:00' AND date <= '2017-08-24 23:59:59'
GROUP By idm
And make the treatment in php, but maybe exist a methode to do that in sql. Can you help me please ? Thx in advance and sorry for my english.
If I correctly understand, this is what you want:
select
idm,
sum(case when date >= '2017-08-23 00:00:00' AND date <= '2017-08-23 23:59:59' then amount end) -
sum(case when date >= '2017-08-24 00:00:00' AND date <= '2017-08-24 23:59:59' then amount end) as diff
from your_table
group by idm
having diff not between -20 and 20
Not tested but this might work..
select (sum(b.amount) - sum(a.amount)) as result, idm
from table a join table b on a.idm = b.idm
where a.date = date('2017-08-23') and b.date = date('2017-08-24')
group by idm
if you can provide a fiddle that might help us help you.
Try that, should be faster than @Shuddh solution because it does not rely on a join
SELECT
idm,
sum(IF(date(`date`) = '2017-08-23', amount, 0)) as amountDay1,
sum(IF(date(`date`) = '2017-08-24', amount, 0)) as amountDay1,
sum(IF(date(`date`) = '2017-08-23', amount, 0))
- sum(IF(date(`date`) = '2017-08-24', amount, 0)) as diffDay1Day2
FROM
table
GROUP BY
idm
My approach would be to calculate the differences per idm and day (which I 'll show you how) and do the rest in the code. So looking at this first part, I am using the datetime manipulation functions to remove the time element out of the datetime values which keeps date only as string. This is now something we can group on:
select sum(amount) as previous_amount, idm, DATE_FORMAT(`date`, '%Y-%m-%d') as day
FROM test
GROUP By idm, day
order by idm, day
This will give you something like:
previous_amount idm day
30 5 2017-08-23
185 5 2017-08-24
13 6 2017-08-23
At this point I would continue in code. However, if you want to do this in the database you need to join the above result table on itself. This simulates a LAG/LEAD behavior (two Oracle instructions that are not available in MySQL). So the query is:
select
diff1.idm,
previous_amount,
actual_amount,
diff2.actual_amount-diff1.previous_amount as difference,
diff1.day as from_day,
diff2.day as to_day
from (
select sum(amount) as previous_amount, idm, DATE_FORMAT(`date`, '%Y-%m-%d') as day
FROM test
GROUP By idm, day
order by idm, day
) as diff1
left join (
select sum(amount) as actual_amount, idm, DATE_FORMAT(`date`, '%Y-%m-%d') as day
FROM test
GROUP By idm, day
order by idm, day
) as diff2 on diff1.idm=diff2.idm
where DATE(diff2.day) > DATE(diff1.day)
and diff2.actual_amount-diff1.previous_amount > 20;
The result of the above looks like:
idm previous_amount actual_amount difference from_day to_day
5 30 185 155 2017-08-23 2017-08-24
Note that the above query guarantees that day2 > day1 but does not enforce one day only difference. However, I think it could get extended to do so fairly easily.
sqlfiddle here
UPDATE #1
If you want to guarantee single day difference then replace the where condition with TIMESTAMPDIFF(DAY, diff1.day, diff2.day) = 1 fiddle