Home > Net >  Get Min and Max after summation in MySql
Get Min and Max after summation in MySql

Time:04-19

I have a dataset as follows,

Table_Date  Description Amount
4/17/2022       A         10
4/17/2022       B         45
4/17/2022       C         34
4/17/2022       D         23
4/17/2022       E         76
4/17/2022       F         45
4/18/2022       A         23
4/18/2022       B         45
4/18/2022       C         67
4/18/2022       D         78
4/18/2022       E         98
4/18/2022       F         54

First I need to get sum of Amount for each day for last 8 days.

So I used following query.

Select Table_Date,sum(Amount) as Total_Amount from usertable where Table_Date>=DATE(NOW()-INTERVAL 8 DAY) group by Table_Date;

Result
    Table_Date  Total Amount
    4/17/2022       233
    4/18/2022       365

Now I need to get the maximum and the minimum after creating this. So I tried as follows,

    select max(Total_Amount) from 
(
select Table_Date,sum(Amount) as Total_Amount from usertable where Table_Date>=DATE(NOW()-INTERVAL 8 DAY) group by Table_Date
) group by Table_Date;

Seems this is not correct. Can someone show me how to get the output as follows ,

Min =233
Max=365

Note : My server do not support window functions

CodePudding user response:

If I understand correctly, you might need to use aggregate function without group by

SELECT MIN(Total_Amount),
       MAX(Total_Amount)
FROM (
    Select Table_Date,sum(Amount) as Total_Amount 
    from usertable 
    where Table_Date>=DATE(NOW()-INTERVAL 8 DAY) 
    group by Table_Date
) t1
  • Related