SQL GROUP BY query

[StewartUK]

Using VFP9 SP2

I have the following SQL:

SELECT TOP 15 Claimed, SUM(Amount)*TaxRates.taxrate/TaxRates.GrossUp AS Total, {} AS FirstDate, {} AS LastDate ; 
  FROM Donation ;
    JOIN TaxRates ON BETWEEN(DonDate, YearStart, YearEnd) ;
  WHERE NOT EMPTY(claimed) ;
INTO CURSOR curTemp GROUP BY 1,3,4 ORDER BY 1 DESCENDING

This gives me the GROUP BY CLAUSE is invalid error. Can someone suggest why that is?

I can run the above as

SELECT TOP 15 Claimed, SUM(Amount)*[COLOR=red]22/78[/color] AS Total, {} AS FirstDate, {} AS LastDate ; 
  FROM Donation ;
    JOIN TaxRates ON BETWEEN(DonDate, YearStart, YearEnd) ;
  WHERE NOT EMPTY(claimed) ;
INTO CURSOR curTemp GROUP BY 1,3,4 ORDER BY 1 DESCENDING

Thanks,

Stewart

 

[StewartUK]

Possibly interesting - I found that after SET ENGINEBEHAVIOR 70, the first SQL worked.

Stewart

 

[alvechurchdata]

SUM(Amount)*TaxRates.taxrate/TaxRates.GrossUp

This puzzles me. That closing bracket looks to be in the wrong place. You're asking it to SUM the Amount so that's got to be done after the grouping; by which time SQL doesn't know which taxrate and grossup to use in the calculation.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[StewartUK]

Well, you put me on the right track Geoff. Moving the closing bracket means that the select runs correctly.

Thanks very much,

Stewart

 

[Mike Lewis]

Stewart,

By the way, you don't need to put columns 3 and 4 in the GROUP BY. In other words, just do GROUP BY 1. The fact that those two columns contain the same value in every row means that the SELECT will satisfy the rules.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[StewartUK]

Hi Mike,

Yes, I was suprised to notice that it worked just with GROUP BY 1.

I hadn't really thought about it, but I suppose I thought it checked the group by before starting, but obviously not. However it does suggest that it scans (in the everyday sense not the VFP sense) the results before grouping.

Interesting.

Stewart

 

[Mike Lewis]

Stewart,

It might be that it simply looks at the expressions, and sees that they don't depend on any of the data in the tables, and so allows the grouping. That would make sense, as it would avoid the need to scan the results before deciding if the grouping was valid. But who knows.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk