In MySQL User table and Login history log table with Left join gives an error for contains nonaggregated column

All we need is an easy explanation of the problem, so here it is.

I am trying to get all user information data that is on the USER table and also only the user’s last login information(which is on another table called LOGIN_HISTORY).

I tried with Left join but MySQL is giving me the error which shows "Expression #15 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'login_history.user_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by"

Here is the USER table

In MySQL User table and Login history log table with Left join gives an error for contains nonaggregated column

Please help!
and this is my LOGIN_HISTORY table.

In MySQL User table and Login history log table with Left join gives an error for contains nonaggregated column

The output which I want is

In MySQL User table and Login history log table with Left join gives an error for contains nonaggregated column

I have tried this query, I don’t know what I am missing but it is not giving me the desired output. I only want all the user information with his LAST login_datetime column. I tried the below query but it is showing all the user records with duplicate and all the login_datetime.

I tried:

select u.id, u.firstname, u.lastname, u.email, u.username, u.password, lh.login_datetime 
from user u 
left join login_history lh on u.id = lh.user_id 
group by u.id  
order by DATE(lh.login_history) desc;`

Please help!

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

An alternative method to that suggested above would be to do the following (fiddle here):

CREATE TABLE user
(
  id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR (256) NOT NULL
);

The number of fields is irrelevant, since you won’t be grouping by them. This is just a sample for demonstration purposes.

Then:

CREATE TABLE login_history
(
  lh_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id INTEGER NOT NULL,
  login_dt TIMESTAMP NOT NULL,
  
  CONSTRAINT lh_user_dt_uq UNIQUE (user_id, login_dt),  -- same user can't login at the same time
  
  CONSTRAINT lh_uid_fk FOREIGN KEY (user_id) REFERENCES user (id)
  
);

Populate:

INSERT INTO user (email)
VALUES
('[email protected]'),
('[email protected]'),
('[email protected]'),
('[email protected]'),
('[email protected]'),
('[email protected]');

and:

INSERT INTO login_history (user_id, login_dt)
VALUES
(1, '2011-01-11'), (1, '2011-02-11'), (1, '2011-03-11'),
(2, '2012-01-12'), (2, '2012-02-12'), (2, '2012-03-12'),
(3, '2013-01-13'), (3, '2013-02-13'), (3, '2013-03-13'),
(4, '2014-01-14'), (4, '2014-02-14'), (4, '2014-03-14'), 
(5, '2015-01-15'), (5, '2015-02-15'), (5, '2015-03-15');

And the query would be:

SELECT u.*, tab.lh_id, tab.ldt
FROM user u
JOIN
(
  SELECT 
    ROW_NUMBER() OVER (PARTITION BY lh.user_id ORDER BY lh.login_dt DESC) AS rn,
    lh_id,
    lh.user_id AS id,
    lh.login_dt AS ldt
  FROM login_history lh
) tab
ON u.id = tab.id
WHERE tab.rn = 1
ORDER BY u.id;

Result:

id  email        lh_id  ldt
1   [email protected]     3   2011-03-11 00:00:00
2   [email protected]     6   2012-03-12 00:00:00
3   [email protected]     9   2013-03-13 00:00:00
4   [email protected]    12   2014-03-14 00:00:00
5   [email protected]    15   2015-03-15 00:00:00

which, by inspection, is correct!

Now, in the fiddle, I’ve done a performance analysis of the query proposed above and @Akina’s answer – and you’ll see that my query is less performant. "So, what’s the point?" you might ask?

Well, imagine a scenario where you want the last 2 logins or 3 or the last x of them… – just change the desired values of rn (put tab.rn <= 2 or tab.rn IN (1,2)– shown at bottom of fiddle)!

Using MySQL’s window functions, in this case the ROW_NUMBER() function (examples here), you can answer many more questions of your data.

I would strongly urge you to become familiar with window functions like those used in the second piece of SQL – they are very powerful and well worth the effort to learn – they will repay that effort 10 times over…

EDIT:

Following a query by the OP concerning users who have never logged in, I did the following (fiddle – same as above).

In order to JOIN the user table to the login_history table with a field that doesn’t exist in the login_history, we have to do a LEFT [OUTER] JOIN (the left join is automatically an outer one, so the OUTER keyword is optional).

I added a user (6, [email protected]) (see above) who has no records in the login table.

Then, I used the following SQL:

SELECT 
  u.id,
  u.email,
  ROW_NUMBER() OVER (PARTITION BY lh.user_id ORDER BY lh.login_dt DESC) AS rn,
  lh.lh_id,
  lh.user_id AS luid,
  lh.login_dt AS ldt
FROM user u
LEFT JOIN login_history lh
ON u.id = lh.user_id
ORDER BY u.id DESC;

Result (snipped for brevity):

id  email         rn    lh_id   luid    ldt
6   [email protected]     1    NULL    NULL   NULL    
5   [email protected]     1      15       5   2015-03-15 00:00:00
5   [email protected]     2      14       5   2015-02-15 00:00:00
...
...

So, we see that through the LEFT JOIN we now have the user 6, but the corresponding records from the login history are NULL (as expected)!

Now we simply wrap the above query in another to get the latest (or 2 latest or 3…) login time(s) for a given user as follows:

SELECT * FROM
(
  SELECT 
    u.id,
    u.email,
    ROW_NUMBER() OVER (PARTITION BY lh.user_id ORDER BY lh.login_dt DESC) AS rn,
    lh.lh_id,
    lh.user_id AS luid,
    lh.login_dt AS ldt
  FROM user u
  LEFT JOIN login_history lh
  ON u.id = lh.user_id
) AS tab
WHERE tab.rn = 1  -- or tab.rn IN (1,2) - shown at the bottom of the fiddle
ORDER BY tab.id;

Result:

id  email          rn  lh_id    luid    ldt
1   [email protected]     1      3       1    2011-03-11 00:00:00
2   [email protected]     1      6       2    2012-03-12 00:00:00
3   [email protected]     1      9       3    2013-03-13 00:00:00
4   [email protected]     1     12       4    2014-03-14 00:00:00
5   [email protected]     1     15       5    2015-03-15 00:00:00
6   [email protected]     1   NULL    NULL    NULL

I also added the results for EXPLAIN ANALYZE (available from MySQL >= 8.0.18), and I also profiled the query (fiddle).

As with the previous solution using the ROW_NUMBER() window function, it’s a bit slower and the plan is more complex but, as I said, it is also potentially far more powerful down the line. I would urge you to test with your own system (H/ware & S/ware) and data to see which solution best suits your requirements.

Method 2

select u.id, u.firstname, u.lastname, u.email, u.username, u.password, 
       MAX(lh.login_datetime) login_datetime
from user u 
left join login_history lh on u.id = lh.user_id 
group by u.id, u.firstname, u.lastname, u.email, u.username, u.password
order by login_history desc;

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply