gets sums on inner joins - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

gets sums on inner joins

Author

Topic

tpiazza55
Posting Yak Master

162 Posts

Posted - 2008-02-21 : 13:57:30

Ok I have two tables

clients and expenses

enpense table columns: clientid, expensetype, expensevalue

clients.clientid and expenses.clientid relate the tow tables on an inner join

what i have is one client that will have multiple expense records in the expense table

cleint1, expensetype1, expensevalue1
cleint1, expensetype2, expensevalue2
cleint1, expensetype3, expensevalue3

what i need to output is client.*, sum(expensevalues), and also expensevalue1, expensevalue2, expensevalue3 as one output line

how do I get this?

tpiazza55
Posting Yak Master

162 Posts

Posted - 2008-02-21 : 15:07:53

i got it -- what a major headache

for anyone else

main query with a left join and a case statement

something like

sum(case when new_categoryname = 'Title Insurance' then new_amount end) as [Title Insurance],

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-02-21 : 15:53:12

You might want to add an "ELSE 0" to your case statement or you might get NULL values for [Title Insurance]...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-22 : 01:42:18

For more information, read about Cross-tab Reports in sql server help file

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources