Mark,
it doesnt work. I try to be more specific

A is invoice header
B is invoice rows

SO I need to
- aggregate rows on the same B.cms_code (something like pn type)
- summing B.pn_value
- filering on A.invoice_Date

With your code something strange happens.... I have value more greater than invoice total.

Codebue Fabio
.--------------------------------.
P-Soft di Codebue Fabio
via Nuova, 9
24060 Tavernola B.sca - BG
Web : www.p-soft.biz
EMail: f.codebue@...


-----Messaggio originale-----
Da: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Per conto di Mark Rotteveel
Inviato: mercoledì 7 dicembre 2011 17:16
A: firebird-support@yahoogroups.com
Oggetto: Re: R: [firebird-support] 1 a n relationship with GROUP BY conditioned on master

On Wed, 7 Dec 2011 17:03:29 +0100, "P-Soft - Codebue Fabio"
<f.codebue@...> wrote:

> I have 2 tables
> A
> fields a1, a2, a3
>
> B
> Fields b1 ,b2, b3, b4
>
> I need to create a SQL retrieve sum(b3) and aggregate on b2 and
> filter

on

> a2 where a1=b1
>
> Some idea?

Something like:

SELECT SUM(b3), b2
FROM A
INNER JOIN B ON B.b1=A.a1
WHERE a2 = ? -- Your filter condition
GROUP BY b2

If that is not what you want, you need to be more specific :)

Mark


------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links