SQL - Sum and Group by Question 
Author Message
 SQL - Sum and Group by Question

I am trying to create a dynaset with the followin SQL:
sSQL = "SelectPerson_ID, age, sum(ben1), sum (ben2) from myTable group by
SelectPerson_ID"

The table's fields are:
Person_ID   Plan_ID age Ben1 Ben2

There may be multiple Plan_IDs for a single Person_ID so the query is
supposed to sum Ben1 and Ben2 for each Person_ID.  Note age is the same for
every unique Person_ID.    

Set ds = MyDB.CreateDynaset(SQL, DbReadOnly)
fails with this SQL.  When I execute the query in VISDATA, it runs but I
get erroneous results that look like:

Person_ID  age Sum(Ben1) Sum(Ben1)

despite the fact that the last element in the query Select is Sum(Ben2).
Can anyone give me some insight into what I am missing and how to fix it?
--
Bob Peticolas
Greenville, RI



Sat, 20 Mar 1999 03:00:00 GMT  
 SQL - Sum and Group by Question

Quote:

> I am trying to create a dynaset with the followin SQL:
> sSQL = "SelectPerson_ID, age, sum(ben1), sum (ben2) from myTable group by
> SelectPerson_ID"

> The table's fields are:
> Person_ID   Plan_ID age Ben1 Ben2

> Set ds = MyDB.CreateDynaset(SQL, DbReadOnly)
> fails with this SQL.  When I execute the query in VISDATA, it runs but I
> get erroneous results that look like:

> Person_ID  age Sum(Ben1) Sum(Ben1)

Try:
SQL = "Select Person_ID, Max(age), sum(ben1), sum (ben2) from myTable group by Person_ID"

I do not know why you did not have a space between "select" and "Person_ID" ... or
why you had "group by SelectPerson_ID" (maybe just typo's) ... or
why you need "age" (if ages are the same for each Person_ID use Max(age), if not then "group by Person_ID, Age") ....
you must group by any non-aggregate columns (aggregate expressions: sum(), min(), max(), count() ect...)

Hope this helps.

--
Thomas Hugel
Brown Distributing Co. (Budweiser)
1330 Allendale Rd.
West Palm Beach, FL 33405



Sun, 21 Mar 1999 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Report showing BOTH Aggregate Sum of Display records and Total Sum by Group

2. SQL Group by (sum)

3. SQL group by (sum)

4. HOW DO I GROUP AND SUM CERTAIN RECORDS IN A SUBFORM

5. Grouping and summing

6. Crystal reports group, sum

7. Group and sum in a 2 d array?

8. Grouping/summing data fields

9. Grouping and summing

10. Summing things for group

11. Sum data by group

 

 
Powered by phpBB® Forum Software