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

Mysql – Datediff in the select clause only returns the first row

For a table like this named Activity:

player_id device_id event_date games_played
1 2 2016-03-01 5
1 2 2016-03-02 6
2 3 2017-06-25 1
3 1 2016-02-02 0
3 4 2018-07-03 5

I wonder why the following query only returns the result on the first row:

select player_id, datediff(event_date, min(event_date)) as date_diff
from Activity

Returns:

player_id date_diff
1 28

I was expecting the fourth row will be returned as it has the minimum event_date, something like

player_id date_diff
2 0
Tags:

2

Answers


  1. 0

    I think you should have 3 separate select query, one to find the min event date in the entire table, one to add the result as a column in the query result, final one to calculate the date difference

    select player_id, datediff(event_date, min_date) as date_diff from (
  select player_id, event_date,  (
    select min(event_date) from Activity) as min_date from Activity) as query;

    From your sample data, running the query would get:

    player_id   date_diff
1   28
1   29
2   509
3   0
3   882
    Login or Signup to reply.
  2. 0

    When I ran the query in your question, I got an error:

    In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column ‘fiddle.ACTIVITY.PLAYER_ID’; this is incompatible with sql_mode=only_full_group_by

    min is an aggregate function and that’s why a GROUP BY is required. However that won’t give you the result you want.

    In order to get difference between EVENT_DATE and minimum EVENT_DATE for each row, you can use a separate SELECT as a generated column.

    select PLAYER_ID
      ,datediff(EVENT_DATE, (select min(EVENT_DATE) from ACTIVITY)) as date_diff
  from ACTIVITY

    Above query returned following result:

    PLAYER_ID date_diff
    1 28
    1 29
    2 509
    3 0
    3 882

    Refer to this db<>fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search