Datediff in the select clause only returns the first row

Question

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

Answer 1

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

Answer 2

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 (for sample data in your question):

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

Refer to this db<>fiddle