Board index » delphi » Local SQL: grouping by month

Local SQL: grouping by month

Hi,

When I try following query on a paradox table I get "Capability not
supported" error.
SELECT a.par, b.model, sum(b.ukupno)
FROM  doksifre a, dokstavk b
WHERE  a.sifra = b.sifra
GROUP BY   a.par, b.model, extract(year from a.date), extract(month from
a.date)

Doksifre(a) is master table, dokstavk (b) is detail table, with key field
SIFRA. Query is supposed to return  number of units (model) sold  by month.
Problem is that Extract function is not supported in GROUP BY clause.

Thanks,
Nik

 

Re:Local SQL: grouping by month


Hi Nik,
You can either create an additional fields in the table that will store year
and month,
or, alternatively, you can make permanent the result of the ungrouped query
with an additional fields (year and month) and run another query on the
newly created table:

Query.SQL.Clear;
Query.SQL.Add('SELECT a.par, b.model, b.ukupno ,extract(year from a.date) as
Year, extract(month from
a.date) as Month
FROM  doksifre a, dokstavk b
WHERE  a.sifra = b.sifra');
Query.Open;
Try
  Check(DbiQInstantiateAnswer
(Query.StmtHandle,NIL,pChar(Query.databasename+'\MyTbl.DB'),szPARADOX, true,
NIL));
Finally
  Query.Close;
End;
Query.SQL.Clear;
Query.SQL.Add('SELECT year, month, par, model, sum(ukupno)
FROM  MyTbl
GROUP BY   par, model, year, month');
Query.Open;

--
Roman
I...@rksolution.cz (please remove STOPSPAM. in header)
Delphi corner now open at my website http://www.rksolution.cz

Quote
Nikola Filipcic wrote in message <6ofppp$3...@forums.borland.com>...
>Hi,

>When I try following query on a paradox table I get "Capability not
>supported" error.
>SELECT a.par, b.model, sum(b.ukupno)
>FROM  doksifre a, dokstavk b
>WHERE  a.sifra = b.sifra
>GROUP BY   a.par, b.model, extract(year from a.date), extract(month from
>a.date)

>Doksifre(a) is master table, dokstavk (b) is detail table, with key field
>SIFRA. Query is supposed to return  number of units (model) sold  by month.
>Problem is that Extract function is not supported in GROUP BY clause.

>Thanks,
>Nik

Other Threads