Question 21642 in Mysql Question posted in
The official MySQL documentation can be found here.

I want to calculate the sum of last transaction for A&B – Mysql

Let’s say the table looks like this:

user id date Amount
123 2022/11/01 5
456 2022/11/02 6
789 2022/11/03 8
123 2022/11/02 9
456 2022/11/04 6
789 2022/11/05 8

I want to calculate the sum of the very last transaction (only one for each user) for A & B FYI I’m using redash and I’m a beginner not sure what other info would you need, I tried MAX but was not sure how to apply it on more than one specific user.

Tags:

3

Answers


  1. 0

    You can try this, where we first calculate the maximum date by user in a common-table expression, then join that result-set to the table to sum the associated values.

    WITH dat
AS
(
SELECT user_id, MAX(date) AS max_date
FROM credit.card
WHERE user_id IN ('A','B','ETC')
GROUP BY user_id
)
SELECT SUM(value) AS sum_on_max_dates
FROM credit.card t
INNER JOIN dat d ON t.user_id = d.user_id AND t.date = m.max_date;
    Login or Signup to reply.
  2. 0

    Get the sum of Amount where user is A or B and date is the most recent date for each user

     SELECT SUM(AMOUNT) AS total
 FROM (
   SELECT AMOUNT, ROW_NUMBER() OVER (PARTITION BY USERID ORDER BY DATE DESC) AS RN
   FROM tableyoudidnotname
   WHERE userid in ('A','B')
 ) X
 WHERE X.RN = 1
    Login or Signup to reply.
  3. 0

    You can try this, Used join with the subquery I mention below.

    SELECT 
  SUM(t1.amount) AS count
FROM
transaction t1
    JOIN
(SELECT 
    user_id, MAX(date) AS max_date
FROM
    transaction
WHERE
    user_id IN ('A', 'B')
GROUP BY user_id) t2 ON t1.user_id = t2.user_id
    AND t2.max_date = t1.date;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search