Board index » delphi » Local SQL, Aggregate field and Group by-problem

Local SQL, Aggregate field and Group by-problem

Hey, does anyone know how to achieve the following.
Get the summed Sale-Values for every month:

SELECT K.SumGross, K.SumNet, K.SumTax,
((EXTRACT( YEAR FROM Date) * 100)  +
(EXTRACT(MONTH FROM Date))) AS Yearmonth
FROM Table1 K
WHERE
(K.Date >= :DatFrom) AND (K.Date <= :DatTil)
ORDER BY Yearmonth
    { HERE COMES THE PROBLEM!}
GROUP BY Yearmonth

Because YearMonth is calculated, TQuery refuses to do the query.
Thanx 4 help
Have fun
Hanz

--

becons...@aol.com

 

Re:Local SQL, Aggregate field and Group by-problem


Quote
>Because YearMonth is calculated, TQuery refuses to do the query.

Loc
--
Brian Bushay (TeamB)
Bbus...@DataGuidance.com

Re:Local SQL, Aggregate field and Group by-problem


Quote
>Because YearMonth is calculated, TQuery refuses to do the query.

Local SQL does not support aggrigation on a calculated field.
Best aproach is to break this into two Queries using DbiMakePerminate
with the first query to create a table to use for the second query

 DbiMakePermanent(query1.handle, 'temp.db',true);

--
Brian Bushay (TeamB)
Bbus...@DataGuidance.com

Other Threads