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
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?
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
Sum of a subquery
Posted - 12/11/2012 : 17:04:31
