Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2007
    Posts
    4

    Unanswered: How to Return Moving Differences with Grouping?

    I'm not even sure if that is the correct terminology, so let me start with some sample data. Say I have a table "data" with colums "id", "datetime", "total".

    Say the data looks something like

    ID DATE TOTAL
    1 2009-10-01 00:00:00 10
    2 2009-10-01 01:00:00 15
    3 2009-10-01 02:00:00 17
    4 2009-10-01 03:00:00 19
    5 2009-10-01 04:00:00 21
    6 2009-10-02 00:00:00 25
    7 2009-10-02 01:00:00 30
    8 2009-10-03 02:00:00 35
    9 2009-10-03 03:00:00 45
    10 2009-10-04 04:00:00 50

    So, what I'd like to return is the total for each day, so what I get back would look something like:

    2009-10-01 11
    2009-10-02 5
    2009-10-03 10
    2009-10-04 10

    Basically what I'm doing is storing a cumulative total that is ever growing, but I'd like to get the totals for each day. Hence, 2009-10-01 saw a growth of 11 (21 - 10).

    I know how to do this through queries/programming. But I'm wondering if there's a more elegant way to do this with just one query.

    Thanks!

  2. #2
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    Why do you want 2009-10-04 10 to be returned? That day has only one total-value.

    For the rest of the rows the following query returns the result you are asking for:
    SELECT date, MAX(total) - MIN(total)
    FROM tablename
    GROUP BY date



    Note that DATE is a reserved word in ANSI SQL. It's a good idea to avoid reserved words as column names. (Or do at least double quote them, i.e "DATE".)

Posting Permissions

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