Geek asks and answers

Mysql payment amount

can someone help me with one request? I have a DB that looks like this: table expenses

paydate       receiver   payment
2011-05-06     SOLO       3000
2011-05-08     Walmart    5000
2011-05-09     McDonalds  400
2011-05-08     Korona     700
2011-05-08     Walmart    1000
2011-05-09     BigZ       1300

I need to calculate the sum of all payments on the day the maximum payment was made. The result should look like this:

paydate       payment
2011-05-08    6700

I managed to find the maximum payment and its payment:

SELECT payment, paydate FROM expenses WHERE payment=(SELECT max(payment) FROM expenses);

but he gives me the maximum payment and payment, and I need the amount of payments on this day.

+3
source share
3 answers
CREATE TABLE `t` (
`t1` INT(10) NULL DEFAULT NULL,
`t2` DATE NULL DEFAULT NULL,
`t3` INT(10) NULL DEFAULT NULL
)


INSERT INTO `t` (`t1`, `t2`, `t3`) VALUES
(1, '2012-04-19', 100),
(2, '2012-04-18', 200),
(3, '2012-04-18', 300),
(4, '2012-04-19', 150);

and request:

select sum(t3), t2 from t  where t2=(select t2 from t where t3 = (select max(t3) from t))

the result gives us 500 and 2012-04-18, i.e. amount and date

or check this:

select t1, sum(t3), t2 from t  where t2=(select t2 from t where t3 = (select max(t3) from t)) group by t1
+1
source

Change the request to this:

select sum(payment), paydate
from expenses 
where paydate=(
  select paydate from expenses 
  where payment = (
    select max(payment) from expenses)
);

where i used the function . SUM

EDIT: , . sqlfiddle , .

+7

As far as I can tell, the other answers did not take into account that there could be more than one date with the same maximum payment. My answer below shows how to return all possible answers:

SELECT sum(payment) total_amount, paydate
FROM expenses
WHERE paydate in (SELECT paydate
                  From expenses
                  WHERE payment = (Select max(payment) from expenses))
group by paydate
order by total_amount desc

Good luck

+2
source

More articles:


All Articles