SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Sum of a subquery
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

centriccd
Starting Member

1 Posts

Posted - 12/11/2012 :  17:04:31  Show Profile  Reply with Quote
I have a DB that has been in use for 8 years. We are changing the data in a couple of fields and I need to update my reporting queries to ensure that they are backwards compatible with the old data.

There are 3 fields in 2 tables:

gen.loan_amt
gen.equity_draw (will be depreciated)
custom_data.value

We are shifting our data as follows:

gen.loan_amt >>> custom_data.value
gen.equity_draw >>> gen.loan_amt

My queries work great for selecting individual rows, but I need to also be able to aggregate these upwards and pull sums.

This is what I have for pulling rows of data:

SELECT COUNT(gen.file_id) AS units,
SUM(CASE WHEN (SELECT custom_data.value
FROM custom_data
WHERE custom_data.field = 'und_08' AND custom_data.file_id = gen.file_id) IS NULL
THEN gen.loan_amt
ELSE
(SELECT custom_data.value
FROM custom_data
WHERE custom_data.field = 'und_08' AND custom_data.file_id = gen.file_id) END) AS maxClaim
FROM gen

What I need is for this query to result is a recordCount (units) and total (maxClaim). Can this be done?

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/11/2012 :  20:01:36  Show Profile  Reply with Quote
Do you mean this

SELECT COUNT(gen.file_id) AS units, 
SUM(Case When c.field = 'und_08' then c.Value Else gen.loan_amt End) as TotalMaxClaim
FROM custom_data c
left join gen gen on gen.file_id = c.file_id
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000