Mysql print SUM() with values of last row-CodePudding

I have a table named transactions:

transaction_date | some_string       | amount
----------------- ------------------- --------
Jan 1            | "old_val"         | 10
Jan 2            | "old_val"         | 20
Jan 3            | "most_rest_val"   | 30
----------------- ------------------- --------

Need to get sum of amount and values of last row like this:

transaction_date | some_string       | SUM(amount)
----------------- ------------------- -------------
Jan 3            | "most_recent_val" | 60
----------------- ------------------- -------------

Tried order by, but doesn't work: Select transaction_date, some_string, SUM(amount) from transactions Order By transaction_date DESC returns Sum with values of first row

Any Idea?

CodePudding user response：

Use a window function instead of an aggregate function to sum up your amount, then ORDER BY .. LIMIT 1 to access only the last row

SELECT
  transaction_date,
  some_string,
  SUM(amount) OVER () AS amount
FROM transactions
ORDER BY transaction_date DESC
LIMIT 1

CodePudding user response：

If you really just want that last row by itself, then use:

SELECT MAX(transaction_date) AS transaction_date,
       'most_recent_val' AS some_string,
       SUM(amount)
FROM transactions;

CodePudding user response：

Use the analytic function SUM OVER:

select 
  transaction_date,
  some_string,
  sum(amount) over () as total
from mytable
order by transaction_date desc
limit 1;

Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=1975a0e977d1757c4c4f6ebd47b3a140