Sum of a subquery - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Sum of a subquery

Author

Topic

centriccd
Starting Member

1 Post

Posted - 2012-12-11 : 17:04:31

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
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-11 : 20:01:36

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources