> >
> > No you dont need to CAST() anything. The Standard SQL rule, incorporated in VFP9, says that for a
> > SELECT columnlist GROUP BY columnlist
> > the two column lists must include all the same columns (eccept for the aggregation function columns, i e the SUM(), COUNT(), MIN(), MAX(), or AVG() group aggegations.
> > Just so the one knows exactly what the sum applies to.
> > Take
> >
custid, amount, city
> > 123 10 London
> > 123 20 Paris
> > 123 5 New York
> >
SELECT custid, city, SUM(amount) FROM table GROUP BY custid
> > It's easy to see that the sum for custid 123 should be 35 but which city is correct?
> >
> > -Anders
>
> Same error, changed code to look like
>
>
> Ryan J. Lashway
>
http://www.lashtech.com* Select TOQtemp
* Goto Top
* Those two lines are superfluous. A SELECT SQL query opens the table in
* the FROM * clause and reads it straight from the harddisk.
Select field1, field2,P_ID,;
Cast(Sum(field4) As N(12,2)) As item1,;
CAST(Sum(field5) As N(12,2)) As item2,;
CAST(Sum(field6) As N(12,2)) As Total;
FROM TOQtemp ;
Group By P_ID, field1, field2
Browse
Note
* Select field1, field2, P_ID
* Group By P_ID, field1, field2
* Same columns in SELECT and GROUP BY lists.
* The rest of the columns are all SUM() aggregations.
* You cannot SELECT nor GROUP BY a column that is aggregated in a SUM()
-Anders