CODEWITHSUNDEEP
mysql
Gipsy
Gipsy

Reputation: 265

mysql sum payments

can anyone help me out with one query? 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 have to calculate the sum of all payments in the day when the max payment was done. the result should look like this: 

paydate       payment
2011-05-08    6700

i managed to find the max payment and the paydate of it:

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

but it only gives me the max payment and paydate, and i need a sum of the payments on that day.

Upvotes: 3

Views: 551

Answers (3)

Mara
Mara

Reputation: 146

As far as I can tell, the other answers did not take into consideration that there can be more than one date with the same max payment. My answer below illustrates how to return all the 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!

Upvotes: 2

hjpotter92
hjpotter92

Reputation: 80639

Change the query to this:

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

where I've used the SUM function.

EDIT: Previous output wasn't what was required. Here is sqlfiddle of what I used previously and now.

Upvotes: 7

llamerr
llamerr

Reputation: 3186

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 query:

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

result gives us 500 and 2012-04-18, i.e. sum 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

Upvotes: 1

Related Questions