Let's say the table looks like this:

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.

CodePudding user response：

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;

CodePudding user response：

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

CodePudding user response：

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;