SELECT statement not displaying data correctly when using variable and reusing it

Question

I have a mysql query which stores the result in variable, but when I use the same variable later, It doesnt return the same value. Am I missing something?

SELECT 
    duration,
    @sum_duration := (SUM(duration) OVER (
        PARTITION BY user_id,`Date`
    )) as sum_duration,
    @sum_duration
    -- 9*3600*1000*(duration/@sum_duration) as normalized_duration
FROM duration_table

The result of the above query is

'duration', 'sum_duration', '@sum_duration'
'86400000', '172800000',    '783413.000000000000000000000000000000'
'86400000', '172800000',    '783413.000000000000000000000000000000'
'86400000', '172800000',    '783413.000000000000000000000000000000'
'86400000', '172800000',    '172800000.000000000000000000000000000000'
'86400000', '172800000',    '172800000.000000000000000000000000000000'
'86400000', '172800000',    '172800000.000000000000000000000000000000'
'86400000', '172800000',    '172800000.000000000000000000000000000000'
'86400000', '172800000',    '172800000.000000000000000000000000000000'
'86400000', '172800000',    '172800000.000000000000000000000000000000'
'86400000', '172800000',    '172800000.000000000000000000000000000000'

Answer 1

This query looks fine:

SELECT 
    duration,
    @sum_duration := (SUM(duration) OVER (PARTITION BY user_id,`Date`))
FROM duration

The window sum without order by gives you the total duration for the same user_id and date.

However I don't see the point for a variable here. I notice this commented line in your query:

SELECT
    duration
    @sum_duration := (SUM(duration) OVER (PARTITION BY user_id,`Date`)),
    9*3600*1000*(duration/@sum_duration) as normalized_duration --> here
FROM duration

This, however, might not work as expected, since MySQL does not guarantees the order of evaluation of expressions in the SELECT clause.

Possibly, you want:

SELECT 
    duration,
    SUM(duration) OVER (PARTITION BY user_id,`Date`) sum_duration,
    9 * 3600 * 1000 * duration 
        / (SUM(duration) OVER (PARTITION BY user_id,`Date`)) normalized_duration    
FROM duration

You could also express this with a nested subquery:

SELECT
    t.*,
    9 * 3600 * 1000 * duration / sum_duration normalized_duration
FROM (
    SELECT 
        duration,
        SUM(duration) OVER (PARTITION BY user_id,`Date`) sum_duration    
    FROM duration
) t