Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2003
    Posts
    2

    Unanswered: query with totals for the last n years

    Hello,

    I have a table that looks like this example:

    Name Year Value
    ...
    John 1995 10
    Peter 1995 5
    Mary 1995 8
    John 1996 7
    Peter 1996 12
    Mary 1996 8
    John 1997 6
    Peter 1997 9
    Mary 1997 14
    John 1998 8
    Peter 1998 7
    Mary 1998 2
    ...

    Now I need a query that produces the totals for the last n years (n could be specified in the query). If n=3:

    Name Year Value
    ...
    John 1997 (10+7+6)=23
    Peter 1997 (5+12+9)=26
    Mary 1997 (8+8+14)=30
    John 1998 (7+6+8)=21
    Peter 1998 (12+9+7)=28
    Mary 1998 (8+14+2)=34
    ...

    I have tried making a crosstab query without much luck. The problem there is how to write it so that only some of the columns were summarized. Is this possible to do without making a lot of complex joins for each year?

    Thanks,
    Martin

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select name, [year], sum(value)
    from yourtable
    where [year]
    between year(date())-3 and year(date())
    group by name, [year]

    rudy
    http://r937.com/

  3. #3
    Join Date
    Jun 2003
    Posts
    2
    Thank you for your suggestion!
    I couldn't get it to work though, maybe because the years are stored as integers and not dates? The "sum(value)" didn't sum up the values between the years either...

    I managed to do a clumsy union-query however:

    SELECT name, "1997" AS Year, Sum(value) AS SumOfValue
    FROM yourtable
    WHERE (((year) Between 1995 And 1997))
    GROUP BY name

    UNION SELECT name, "1998" AS Year, Sum(value) AS SumOfValue
    FROM yourtable
    WHERE (((year) Between 1996 And 1998))
    GROUP BY name

    UNION SELECT ...

    And I'm pleased with that, but I'm grateful if there is a better solution.

    Regards
    Martin

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •