How to query a nonaggregated column using group by in MySql?

Question

I have a usecase where I want to fetch the latest transaction made by a user on the previous day,
Eg. If today is 26th June 2019,
I want to fetch the last transaction made by the user on 25th June 2019 at 23:59:59

My data is like this -

I'm not good at SQL and tried the following query -

SELECT MAX(transaction_id), user_id, MAX(created_date), new_balance  
FROM transaction
where created_date < 1561507199 
GROUP BY user_id;

I'm getting the following error -

Error Code: 1055. Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'transaction.new_balance' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

score 1 · Accepted Answer · answered Jun 26 '19 at 02:31

1

You were close / on the right track. For a given user, you want the highest transaction ID for that user LESS than the given created date in question. Once you have that, then grab the full detail line from transactions table.

select
      T2.*
   from
      ( select user_id, max( transaction_id ) maxTransID
           from transaction
           where created_date < 1561507199
           group by user_id ) Tmp
         JOIN Transaction T2
            on Tmp.MaxTransID = T2.Transaction_ID

We can join on the maximum transaction ID because it will only ever exist for one user.

answered Jun 26 '19 at 02:31

DRapp

47,638
12
72
142

Hey, wanted to know how I could calculate SUM of T2.new_balance ? Or will I need to hold the resultSet of this query and calculate sum on the variable? – Dev1ce Jun 26 '19 at 10:51
@AniruddhaRaje, Sum of NewBalance does not make sense, unless you wanted the total of all users based on their last transaction (such as above answer) for balance as of a given day for everyone. What context would you want such an answer? – DRapp Jun 26 '19 at 11:19
Actually, from the resultSet of your query, I also wanted to calculate sum of T2.new_balance, so as to calculate the total balance of all the users for that day. – Dev1ce Jun 26 '19 at 11:23

score 1 · Answer 2 · answered Mar 07 '20 at 18:25

You can use ANY_VALUE for the non-aggregated columns. In your case the non-aggregated column was new_balance. So, you could use:

SELECT MAX(transaction_id), user_id, MAX(created_date), ANY_VALUE(new_balance)
FROM transaction
where created_date < 1561507199 
GROUP BY user_id;

More details here.

How to query a nonaggregated column using group by in MySql?

2 Answers2