SQL: Select group by and its last transaction date-CodePudding

my table looks like this

   id | email         | To  | amount | date
--------------------------------------------------
    1 | [email protected] | bob | 100    | 12/may/2022
    2 | [email protected] | jon | 100    | 12/may/2022
    3 | [email protected] | ben | 50     | 12/may/2022
    4 | [email protected] | bob | 25     | 13/may/2022
    5 | [email protected] | jon | 10     | 14/may 2022

i want to group by "to" and sum the amount of each group and also get the latest transaction date of a particular group

My current query is:

select to, sum(amount) as "amount"
from transactions
where email = '[email protected]'
group by to

this gives me

  to  | amount
-----------------
  bob | 125
  jon | 100

I want to get the latest transaction date also

to  | amount | date
bob | 125    | 13/may/2022
jon | 100    | 12/may/2022

CodePudding user response：

if the datatype of column date is DATETIME then try below query

select to, sum(amount) as "amount", MAX(date) as date
from transactions
where email = '[email protected]'
group by to

Or convert date column to datetime and take MAX value in select query