Dave Molinero Dave Molinero - 6 months ago 14
MySQL Question

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

It was very tricky to figure out what to title this question, so if anyone has any ideas for improvements feel free to edit :-).

Here's the deal. I have a MySQL table that includes a bunch of donations, and there's a date for each donation. I also have a

years_active
column. I need to run a query that will
SET
the years active for every row to 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'd 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 |
------------------------------------------------------------


I know that it's far from ideal to be storing the
years_active
redundantly in multiple rows like this. Unfortunately this table is for data visualizations and with my software I have absolutely no ability to restructure the data altogether; the
years_active
MUST be in every row.

In my research it seems like I would use subqueries to get the
MIN
value for each user id and the
MAX
value for each unique user id, and then do a
DATEDIFF
on those, and set the result to the column. But I don't really understand how I would run all these queries over and over again for every unique user.

Can someone point me in the right direction? Is this possible?

Answer
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

Follow the link below for a running demo:

SQLFiddle

Update:

Here is an UPDATE statement which will assign the years_active column the correct values:

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