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

> Johan,
>
> sorry - haven't read closely enough.
>
> > 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).
>
> You don't your artno be part of the group by, you just want the

artno be

> neglectable but be part of the result set?
>
> You have to use a stored proc for doing so, I don't think you can
> achieve this with ONE query.
> (The old problem: you cannot select n columns and return n+x

columns).

>
>
> > 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.
>
> Luc.

The "artno" is used to select xxxx or yyyy for making it to two
columns in the result.

Any ideas on how to make that kind of SP? Thats new for me!

Thanks for helping.
/Johan