
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