Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2004
    Posts
    5

    Exclamation Unanswered: sum DISTINCT rows

    People:
    See if you can figure this out. I am trying to Sum([UNITS]) for all DISTINCT rows of [START]. Here's how I do it with a PHP loop:

    Sample_Table
    _______________
    START--END--UNITS
    0--0--0.00
    0--1--0.25
    1--0--0.25
    1--1--0.00
    _______________
    ...
    $START = 0;
    while ($START < 2)
    {
    $query = odbc_exec($odbc, "SELECT Sum([UNITS]) AS [SUMMED_U] FROM [Sample_Table] WHERE [START] = $START");
    $START++;
    }
    ...

    Here, it was easy to loop through all incremental integer values of [START]. But what if the table is stacked differently, like so:

    Sample_Table
    _______________
    START--END--UNITS
    NYC--0--0.00
    NYC--1--0.25
    LAX--0--0.25
    LAX--1--0.00
    _______________

    How would I now Sum([UNITS]) for all DISTINCT rows of [START]??
    Thanks, googler1
    Last edited by googler1; 12-10-04 at 18:42.

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    I think this is what your after

    Code:
    SELECT Sample_Table.FROM Sample_Table.TO, Sum([UNITS]) AS [Sum]
    FROM Sample_Table
    GROUP BY Sample_Table.FROM, Sample_Table.TO;
    HTH

    Dave

  3. #3
    Join Date
    Dec 2004
    Posts
    5
    Thanks Dave, but it does not work. The query that I am after should output something like this (refer to Sample_Table above):

    Query_Table
    ________________
    START--SUMMED_U
    NYC--0.25 (0.00 + 0.25)
    LAX--0.25 (0.25 + 0.00)
    ________________
    googler1
    Last edited by googler1; 12-10-04 at 18:43.

  4. #4
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    SELECT Sample_Table.FROM , Sum([UNITS]) AS [Sum]
    FROM Sample_Table
    GROUP BY Sample_Table.FROM;

    ok that should work for you then

    Dave

  5. #5
    Join Date
    Dec 2004
    Posts
    5
    Thanks Dave, but I do NOT think that will work. The query would have to be a little more intricate and possibly include a DISTINCT row expression.

    I'll see if I can move this post to the SQL forum- keep an eye on this there.
    googler1
    Last edited by googler1; 12-10-04 at 18:29.

  6. #6
    Join Date
    Dec 2004
    Posts
    5
    I figured it out myself by combining "SELECT DISTINCT [START]" with "Sum([UNITS]) AS [SUMMED_U]"- much simpler in hindsight (and it is much quicker than having PHP loop through the entire table).
    googler1
    Last edited by googler1; 12-10-04 at 18:44.

  7. #7
    Join Date
    Dec 2004
    Posts
    5
    Close Thread

Posting Permissions

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