September 25, 2009 at 5:15 am
Hello,
How can I sum columns from 3 other transactional tables?
Example pseudocode
For every item in table1,
CASE WHEN(sum(table2.column1) - sum(table3.column1) - sum(table4.column1) > 100 THEN 1 ELSE 0 END)
Can this be done?
September 25, 2009 at 6:45 am
You can JOIN the tables and use Sum function.
Select a.col1, Case when (Sum(b.Col3) - Sum(b.Col4) < 0)
THEN 1 Else 0 END as ColName
from dbo.table1 as a
JOIN
dbo.Table2 as b
on a.Col1 = b.Col2
Where a.col7 = 'Test'
group by a.col1
-Roy
September 25, 2009 at 3:28 pm
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, purchase and warehousetable.
I believe there must be a derived table involved but I'm not sure about the syntax
See attached image for an explanation with tabledata.
September 27, 2009 at 4:42 am
You want to use a windowed aggregate for that.
Check out BOL for information on the OVER clause, paying particular attention to the partition by bit. There's some pretty detailed examples of how to do that.
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply