Geek Answers Handbook

In MySQL, set a value in each row to calculate DATEDIFF on the same rows

It was very difficult to figure out what to call this question, so if anyone has ideas for improvement, you can freely edit :-).

Here's the deal. I have a MySQL table that includes a bunch of donations, and there is a date for each donation. I also have a column years_active. I need to run a query that will be the SETyear active for each row for the difference (in years) from the first date to the last date for each unique user.

So this is my starting table:

------------------------------------------------------------
|  user_id    |   donation    |    date     | years_active |
------------------------------------------------------------
|     1       |     $10       | 2002-01-01  |     null     |
|     1       |     $15       | 2005-01-01  |     null     |
|     1       |     $20       | 2009-01-01  |     null     |
|     2       |     $10       | 2003-01-01  |     null     |
|     2       |     $5        | 2006-01-01  |     null     |
|     3       |     $15       | 2001-01-01  |     null     |
------------------------------------------------------------

And this is the table I would like to achieve:

------------------------------------------------------------
|  user_id    |   donation    |    date     | years_active |
------------------------------------------------------------
|     1       |     $10       | 2002-01-01  |     8        |
|     1       |     $15       | 2005-01-01  |     8        |
|     1       |     $20       | 2009-01-01  |     8        |
|     2       |     $10       | 2003-01-01  |     4        |
|     2       |     $5        | 2006-01-01  |     4        |
|     3       |     $15       | 2001-01-01  |     1        |
------------------------------------------------------------

, years_active , . , , ; years_active .

, , MIN MAX , DATEDIFF . , .

- ? ?

+4
1
SELECT t1.user_id, t1.donation, t1.date, t2.years_active
FROM yourTable t1
INNER JOIN
(
    SELECT user_id, MAX(YEAR(date)) - MIN(YEAR(date)) + 1 AS years_active
    FROM yourTable
    GROUP BY user_id
) t2
    ON t1.user_id = t2.user_id

:

SQLFiddle

Update:

UPDATE, years_active :

UPDATE yourTable t1 
INNER JOIN
(
    SELECT user_id, MAX(YEAR(date)) - MIN(YEAR(date)) + 1 AS years_active
    FROM yourTable
    GROUP BY user_id
) t2
    ON t1.user_id = t2.user_id
SET t1.years_active = t2.years_active
+8

More articles:


All Articles