CODEWITHSUNDEEP
sql-server
vvp1
vvp1

Reputation: 21

SQL Server find sum of values based on criteria within another table

I have a table consisting of ID, Year, Value

---------------------------------------
|   ID     |     Year    |    Value   |
---------------------------------------
|   1      |     2006    |     100    |
|   1      |     2007    |     200    |
|   1      |     2008    |     150    |
|   1      |     2009    |     250    |
|   2      |     2005    |     50     |
|   2      |     2006    |     75     |
|   2      |     2007    |     65     |
---------------------------------------

I then create a derived, aggregated table consisting of an ID, MinYear, and MaxYear

---------------------------------------
|   ID     |   MinYear   |   MaxYear  |
---------------------------------------
|   1      |     2006    |    2009    |
|   2      |     2005    |    2007    |
---------------------------------------

I then want to find the sum of Values between the MinYear and MaxYear foreach ID in the aggregated table, but I am having trouble determining a proper query.

The final table should look something like this

----------------------------------------------------
|   ID     |   MinYear   |   MaxYear  |   SumVal   |
----------------------------------------------------
|   1      |     2006    |    2009    |     700    |
|   2      |     2005    |    2007    |     190    |
----------------------------------------------------

Right now I can perform all the joins to create the second table. But then I use a fast forward cursor to iterate through each record of the second table with the code inside the for loop looking like the following

DECLARE @curMin int
DECLARE @curMax int
DECLARE @curID  int
FETCH Next FROM fastCursor INTo @curISIN, @curMin , @curMax 
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT Sum(Value) FROM ValTable WHERE Year >= @curMin and Year <= @curMax and ID = @curID
Group By ID
FETCH Next FROM fastCursor INTo @curISIN, @curMin , @curMax

Having found the sum of values between specified years, I can connect it back to the second table and I wind up the desired result (the third table).

However, the second table in reality is roughly 4 million rows, so this iteration is extremely time consuming (~generating 300 results a minute) and presumably not the best solution.

My question is, is there a way to generate the third table's results without having to use a cursor/for loop?

Upvotes: 0

Views: 71

Answers (3)

Hogan
Hogan

Reputation: 70538

During a group by the sum will only be for the ID in question -- since the min year and max year is for the ID itself then you don't need to double query. The query below should give you exactly what you need. If you have a different requirement let me know.

SELECT ID, MIN(YEAR) as MinYear, MAX(YEAR) as MaxYear, SUM(VALUE) as SUMVALUE
FROM tablenameyoudidnotsay
GROUP BY ID

Upvotes: 1

Tony Dong
Tony Dong

Reputation: 3313

You could use query as bellow TableA is your first table, and TableB is the second one

SELECT *,
(select SUM(Value) FROM TableA where tablea.ID=TableB.ID AND tableA.Year BETWEEN 
TableB.MinYear AND TableB.MaxYear) AS SumValue
from TableB

Upvotes: 0

Simon
Simon

Reputation: 1081

You can put your criteria into a join and obtain the result all as one set which should be faster:

SELECT b.Id, b.MinYear, b.MaxYear, sum(a.Value)
FROM Table2 b
JOIN Table1 a ON a.Id=b.Id AND b.MinYear <= a.Year AND b.MaxYear >= a.Year
GROUP BY b.Id, b.MinYear, b.MaxYear

Upvotes: 0

Related Questions