--- In firebird-support@yahoogroups.com, Lucas Franzen <luc@r...>
wrote:

>
> Johan,
>
>
> johanfredse schrieb:
>
> > Hi!
> >
> > I'm trying to solve the following... (In FB1.5 RC7)
> >
> > Having a table with the columns:
> > dlink regdate worker wtype artno pcs nett gross
> > 12345 12/11/2003 MS 1 xxxx 2.5 150 195
> > 12345 12/11/2003 MS 1 yyyy 1.0 75 105
> > 12345 12/11/2003 MS 1 yyyy 4.0 11 23
> > 12345 13/11/2003 MS 2 xxxx 0.7 150 195
> > 12345 13/11/2003 MS 2 yyyy 2.3 375 525
> > 12351 13/11/2003 MS 1 xxxx 0.7 150 195
> > 12351 13/11/2003 MS 1 yyyy 2.3 375 525
> >
> > What I need out of this is a resultset grouped on dlink, regdate,
> > worker and wtype.
> > Each row should contain sum_of_xxxx(nett*pcs), sum_of_xxxx

(gross*pcs),

> > sum_of_yyyy(nett*pcs), sum_of_yyyy(gross*pcs).
> >
> > Resulting in
> > 12345, 12/11/2003, MS, 1, xxxx nett, xxxx gross, yyyy nett, yyyy

gross

> > 12345, 13/11/2003, MS, 2, xxxx nett, xxxx gross, yyyy nett, yyyy

gross

> > 12351, 13/11/2003, MS, 1, xxxx nett, xxxx gross, yyyy nett, yyyy

gross

> > as a result of the query.
>
> How about:
>
> SELECT
> dlink, regdate, worker, wtype,
> SUM ( artno ),
> SUM ( pcs ),
> SUM ( nett ),
> SUM ( gross )
> FROM table
> GROUP BY
> dlink, regdate, worker, wtype,
>
> ?
>
> Luc.

That will not give me the calulation (nett*pcs) and (gross*pcs)
before the sum is done and it will also not result in xxxx, yyyy as
two columns. But the grouping it gives is correct.

I think some nested select statements is needed...? or...

/Johan