Sql Sum of different rows

# Sql Sum of different rows

• Wednesday, February 01, 2012 10:04 PM

I am looking to sum up a column Gross_88ths_Value based on the vaule of Main_Account column and the different vaules it can have. I know the statment below is not right but hoping for some advice.

SELECT
sum(GL.Gross_88ths_Value) as Gross_88ths_Value
FROM GL_Detail as GL
WHERE (GL.Well_Code = @WellCode)
AND (GL.Main_Account = 305)
AND (GL.Main_Account = 310)
AND (GL.Main_Account = 320)
AND (GL.Main_Account = 321)
AND (GL.Main_Account = 322)
AND (GL.Main_Account = 323)
AND (GL.Main_Account = 324)
AND (GL.Main_Account = 325)
AND (GL.Main_Account = 326)
Group by GL.Well_Code

### All Replies

• Wednesday, February 01, 2012 10:59 PM

This works for me but looking for more efficient way??

Declare @WellCode char(12) = 'EFKBAR0001'
Declare @TotalCapitalCost money = 0
Declare @Acct305 money = 0
Declare @Acct310 money = 0
Declare @Acct320 money = 0
Declare @Acct321 money = 0
Declare @Acct322 money = 0
Declare @Acct323 money = 0
Declare @Acct324 money = 0
Declare @Acct325 money = 0
Declare @Acct326 money = 0

Set @Acct305 = (SELECT sum(GL.Gross_88ths_Value) FROM GL_Detail as GL WHERE (GL.Well_Code = @WellCode) AND (GL.Main_Account = 305) GROUP BY GL.Well_Code)
Set @Acct310 = (SELECT sum(GL.Gross_88ths_Value) as Gross_88ths_Value FROM GL_Detail as GL WHERE (GL.Well_Code = @WellCode) AND (GL.Main_Account = 310)GROUP BY GL.Well_Code)
Set @Acct320 = (SELECT sum(GL.Gross_88ths_Value) as Gross_88ths_Value FROM GL_Detail as GL WHERE (GL.Well_Code = @WellCode) AND (GL.Main_Account = 320)GROUP BY GL.Well_Code)
Set @Acct321 = (SELECT sum(GL.Gross_88ths_Value) as Gross_88ths_Value FROM GL_Detail as GL WHERE (GL.Well_Code = @WellCode) AND (GL.Main_Account = 321)GROUP BY GL.Well_Code)
Set @Acct322 = (SELECT sum(GL.Gross_88ths_Value) as Gross_88ths_Value FROM GL_Detail as GL WHERE (GL.Well_Code = @WellCode) AND (GL.Main_Account = 322)GROUP BY GL.Well_Code)
Set @Acct323 = (SELECT sum(GL.Gross_88ths_Value) as Gross_88ths_Value FROM GL_Detail as GL WHERE (GL.Well_Code = @WellCode) AND (GL.Main_Account = 323)GROUP BY GL.Well_Code)
Set @Acct324 = (SELECT sum(GL.Gross_88ths_Value) as Gross_88ths_Value FROM GL_Detail as GL WHERE (GL.Well_Code = @WellCode) AND (GL.Main_Account = 324)GROUP BY GL.Well_Code)
Set @Acct325 = (SELECT sum(GL.Gross_88ths_Value) as Gross_88ths_Value FROM GL_Detail as GL WHERE (GL.Well_Code = @WellCode) AND (GL.Main_Account = 325)GROUP BY GL.Well_Code)
Set @Acct326 = (SELECT sum(GL.Gross_88ths_Value) as Gross_88ths_Value FROM GL_Detail as GL WHERE (GL.Well_Code = @WellCode) AND (GL.Main_Account = 326)GROUP BY GL.Well_Code)

Set @TotalCapitalCost = IsNull(@Acct326,0) + IsNull(@Acct325,0) + IsNull(@Acct324,0) + IsNull(@Acct323,0) + IsNull(@Acct322,0) + IsNull(@Acct321,0) + IsNull(@Acct320,0) + IsNull(@Acct310,0) + IsNull(@Acct305,0)
Select @TotalCapitalCost

• Friday, February 03, 2012 6:25 AM
Moderator

Hi jribinsontx,

Welcome to the MSDN forum!

Since the current forum is for C# questions, you may ask the questions in the following SQL forum: