Sql Sum of different rows

Question

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

Answer 1

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

Answer 2

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:

http://social.msdn.microsoft.com/Forums/en/transactsql/threads

SQL Server Developer Center > SQL Server Forums > Transact-SQL

Thanks.

yoyo

---

Yoyo Jiang[MSFT]
MSDN Community Support | Feedback to us