Hi!

SELECT a.CodeAg, t.Category, t.Target, sum(s.Amount)
FROM AGENTS a
JOIN TARGET t ON a.CodeAg = t.CodeAg
JOIN PRODUCTS p ON t.Category = p.Category
JOIN SALES s ON p.ProductID = s.ProductID
GROUP BY 1, 2, 3

For this to work as you want, there should be no duplicates of
a.CodeAg, t.Category and p.ProductID (if there is, you have to be
more careful in you coding).

And you wrote your question very clearly, thanks.

HTH,
Set

--- In firebird-support@yahoogroups.com, "laurstorage" wrote:
>
> Hello!
> I am a new user of Firebird and i want to do some
> things, but my knowledge is far less than the problem
>
> So, i want to ask for help, if is not very complicate
> and time consumming.
>
> So, I have 4 tables:
>
> - Agents (with fiels)
> CodeAg (char) - John. Mike
> Name (char) - John Doe, Mike Johnson, etc
>
> - Target (with fiels)
> CodeAg (char) - John
> Category (char) - Beverages, Condiments, etc
> Target (numeric) - 1000, 2000
>
> - Products (with fields)
> ProductID (char) - 1, 2, 3, etc
> Category (char) - Beverages, Condiments, etc
>
> - Sales (with fields)
> ProductID (char) - 1, 2, 3, etc
> Amount (number)
>
> So, i want to generate in a grid the following report
> (for every agent in the table AGENTS):
> CodeAg; Category; Target; Sum
(Sales.Amount)
> group
> by category;
> John Beverages 1000 100
> John Condiments 2000 500
> Mike Beverages 1500 1700
> Mike Condiments 2000 2500
>
> etc
>
> I try to describe things as clear as possible
>
> So, if can someone help me, it will be awsome for me..
>
> thanks a lot