Google Answers Logo
View Question
Q: SQL: Sum over Groups ( Answered 5 out of 5 stars,   0 Comments )
Subject: SQL: Sum over Groups
Category: Computers > Programming
Asked by: jennybo-ga
List Price: $2.00
Posted: 23 Jul 2003 15:08 PDT
Expires: 22 Aug 2003 15:08 PDT
Question ID: 234333

I have the following table:

Categorie   ID   Date         Time     Demand    RemDemand
A           01  01.01.2003    19:35     34            ?
A           02  03.04.2003    10:34     12            ?
A           03  01.01.2003    10:25     52            ?
B           04  01.01.2003    11:28     18            ?
C           05  23.04.2003    17:54     09            ?

ID is the primary key. 

I am trying to calculate the remaining demand for each ID. This is the
sum of B.Demand of all datasets with the same category, same date and
larger time:

Categorie   ID   Date         Time     Demand    RemDemand
A           01   01.01.2003   19:35      34          34
B           02   03.04.2003   10:34      12          12
A           03   01.01.2003   10:25      52         105
A           04   01.01.2003   11:28      19          53
C           05   23.04.2003   17:54      09          09

I tried the following query:

SELECT B.Categorie, B.ID, B.Date, B.Time, Sum(B.Demand) AS
RemDemand INTO Temp
FROM Customers AS A, Customers AS B
GROUP BY B.Categorie, B.ID, B.Date, B.Time
HAVING (((A.Categorie)=B.Categorie) And ((A.Date)=B.Date) And

This doesn't seem to work. I get an error message saying that B.ID is
not destinct (I am not using the English Version of Access).

What would be the correct query?

Best regards,

Jennifer Borck
Subject: Re: SQL: Sum over Groups
Answered By: mathtalk-ga on 23 Jul 2003 18:19 PDT
Rated:5 out of 5 stars
Hi, Jennifer:

Excusez-moi, but the data you present in the "results" appears to be
inconsistent with the "input" data.  For the sake of consistency and
expediency, I've constructed an Access 2002 table called Customers
with the following entries:

Categorie   ID   Date         Time     Demand 
A           01  01.01.2003    19:35     34    
B           02  03.04.2003    10:34     12    
A           03  01.01.2003    10:25     52    
A           04  01.01.2003    11:28     19    
C           05  23.04.2003    17:54     09    
ID is still the table's primary key, but I've swapped a couple of the
values in the first column and corrected one value in the final
"Demand" column.

Basically your question is about constructing a query that adds a
further "computed" column for "remaining demand" or RemDemand.

The clearest way to state this is probably using a dependent query,
rather than as a join, but since you've already gone down the path of
thinking about this as a join (and since Access is better with joins
than dependent queries), let's complete the journey:

SELECT A.Categorie, A.ID, A.Date, A.Time,
       A.Demand, SUM(B.Demand) As RemDemand
FROM Customers As A, Customers As B
WHERE A.Categorie = B.Categorie
 AND  A.Date = B.Date
 AND  A.Time <= B.Time
GROUP BY A.Categorie, A.ID, A.Date, A.Time, A.Demand

You can simply paste this into the SQL view of the Query designer of
Access.  The results should look, as you expected, like this:

Categorie ID    Date     Time    Demand    RemDemand
A          1  1/01/2003  19:35    34        34
B          2  4/03/2003  10:34    12        12
A          3  1/01/2003  10:25    52        105
A          4  1/01/2003  11:28    19        53
C          5  4/23/2003  17:54    9         9

albeit with your default European date format (rather than my American

The HAVING clause (which you attempted to use) would filter on the
records _after_ the GROUP BY clause has worked its magic.  Note that I
used simply a WHERE clause, which acts on the "raw join" before any
GROUP BY totaling takes place.  The final ORDER BY clause is mostly
cosmetic, to put the output records in key order.

Please let me know if I can further clarify the SQL statement or any
other aspect of my answer.

regards, mathtalk-ga
jennybo-ga rated this answer:5 out of 5 stars
Excellent. Thank you!

There are no comments at this time.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy