Ok now it makes perfect sense.

I was thinkning in the wrong direction 🙂

Thanks very much

Ok but from and to values are unknown

Not quite sure how you mean?

works great!

thanks alot

ok thats one way of doing it 🙂

Hello, thanks but I am not quite sure I understand it.

Let me take a new example.

I have 4 tables:

Items

Orders

Purchase

Warehouse

I want to sum, for every item in itemstable, quantity in orders,...

Great stuff.

Im on sql 2000 here som im not able to use cte, sorry.

@r.hensbergen, your query works perfect but I dont see the logic in it.

Can you explain...

SELECT table1.id,

table1.[desc],

table2.loc

sum(table2.qty1),

sum(table3.qty2)

FROM table2

INNER JOIN table1 on table2.id = table1.id

INNER JOIN table3 on table2.id = table3.id

where table1.id = '45'

GROUP BY table2.loc, table1.id, table1.[desc]

But this doesnt seem to be right

sweet!

Thanks alot.

No, the query then returns

BLUE 10 1

It should be

BLUE 10 4

Ok this is great stuff but if I add this row to table1

location number quantity

BLUE ...

Great!

Both solutions work but I will go with the case scenario.

Thanks alot.

Would it be possible to group quantity and cost for lets say last month? and the month before that?

ID...

I'm not familiar with sql derived querys. Can you give me an example?