CODEWITHSUNDEEP
sqlsql-server
user2501239
user2501239

Reputation: 149

How to sum a column after it is filtered by a where clause

I am attempting to sum the results of a query after it has been filtered using a where clause. However, when I have tried, it sums the entire column amount disregarding the filtered results. Here is the current code I am using: 

FROM [MBS_STATS].[dbo].[Performance_2012Q2]



  Where dbo.Performance_2012Q2.[Current Interest Rate] >= 3.00 AND dbo.Performance_2012Q2.[Current Interest Rate] < 3.10 and dbo.Performance_2012Q2.[Monthly Reporting Period] = '03/01/2013'


Select SUM (Convert (float,(dbo.Performance_2012Q2.[Current Actual Unpaid Principal Balance]))) from dbo.Performance_2012Q2

Can anyone tell me how to sum the query results produced by the where clause? Thank you!

I am using SQL Server 2012

Upvotes: 0

Views: 144

Answers (2)

iruvar
iruvar

Reputation: 23374

you appear to be suffering from a fundamental misunderstanding of how SQL is supposed to work. You need to aggregate and filter in a single query

Select SUM (Convert (float,(dbo.Performance_2012Q2.[Current Actual Unpaid Principal   Balance]))) from dbo.Performance_2012Q2
Where dbo.Performance_2012Q2.[Current Interest Rate] >= 3.00 AND dbo.Performance_2012Q2.[Current Interest Rate] < 3.10 and dbo.Performance_2012Q2.[Monthly Reporting Period] = '03/01/2013'

Upvotes: 2

Aleks G
Aleks G

Reputation: 57306

You still write the query in the standard way:

Select SUM (Convert (float,(dbo.Performance_2012Q2.[Current Actual Unpaid Principal Balance])))
from dbo.Performance_2012Q2 
Where dbo.Performance_2012Q2.[Current Interest Rate] >= 3.00
And dbo.Performance_2012Q2.[Current Interest Rate] < 3.10
And dbo.Performance_2012Q2.[Monthly Reporting Period] = '03/01/2013'

Database engine will first apply your WHERE clause and then aggregate the results.

Upvotes: 2

Related Questions