How can I sum the Case Statement?

[demausdauth]

working in VS 2005 Version 8
sql server 2005


Here is my select statement:

SELECT     Agency, Type, CompanyNumber, AgencyDirect, PolicyNumber, Date, KeyTranCode, Time, UpdateTime, InstTime, CustomerNumber, PolicySeq, 
                      TranCode, UpdateDate, EffDate, LobCode, GrossPrem, SplitPrem, CurrComm, PrevComm, SplitComm, ProdSplit, PayableSelected, PayableFromInstall, 
                      BusinessType, AcctType, CommMarked, CommReconciled, BrokerNumber, CommSW, 
                      CASE AcctType WHEN 'C' THEN CASE AgencyDirect WHEN 'D' THEN CommMarked ELSE CurrComm END WHEN 'A' THEN (CurrComm - PrevComm) 
                      END AS CommTotals
FROM         Payables
WHERE     (PayableSelected = 1) AND (Type = 'C')
GROUP BY Agency, Type, CompanyNumber, AgencyDirect, PolicyNumber, Date, KeyTranCode, Time, UpdateTime, InstTime, CustomerNumber, PolicySeq, 
                      TranCode, UpdateDate, EffDate, LobCode, GrossPrem, SplitPrem, CurrComm, PrevComm, SplitComm, ProdSplit, PayableSelected, PayableFromInstall, 
                      BusinessType, AcctType, CommMarked, CommReconciled, BrokerNumber, CommSW

I would like to get a sum of the Case statement into another column, don't really care if it shows up multiple times.
Is it even possible to get the sum that I want through sql?

[iburyak]

Not sure if I understood your problem correctly but it should be as simple as

[PHP]SELECT Agency, Type, CompanyNumber, AgencyDirect, PolicyNumber, Date, KeyTranCode, Time,
UpdateTime, InstTime, CustomerNumber, PolicySeq,
TranCode, UpdateDate, EffDate, LobCode, GrossPrem, SplitPrem,
CurrComm, PrevComm, SplitComm, ProdSplit, PayableSelected, PayableFromInstall,
BusinessType, AcctType, CommMarked, CommReconciled, BrokerNumber, CommSW,
sum(CASE AcctType WHEN 'C' THEN
CASE AgencyDirect WHEN 'D' THEN CommMarked ELSE CurrComm END
WHEN 'A' THEN (CurrComm - PrevComm)
END) AS CommTotals
FROM Payables
WHERE (PayableSelected = 1) AND (Type = 'C')
GROUP BY Agency, Type, CompanyNumber, AgencyDirect, PolicyNumber, Date, KeyTranCode, Time,
UpdateTime, InstTime, CustomerNumber, PolicySeq,
TranCode, UpdateDate, EffDate, LobCode, GrossPrem, SplitPrem, CurrComm, PrevComm, SplitComm,
ProdSplit, PayableSelected, PayableFromInstall,
BusinessType, AcctType, CommMarked, CommReconciled, BrokerNumber, CommSW [/PHP]

[demausdauth]

I may not have been clear I would like to get a sum of CommTotals column and put that into another column. I have tried summing the case statement the way
- iburyak - suggested but that will give me a total on the returned row. I basically would like to have SUM(CommTotals) but I get an invalid column error.

hope this helps for anyone else trying to figure it out
and thanks for any help

[almaz]

Your GROUP BY clause includes all fields, so the result of SUM() function will be the same as its argument. If you want to sum up CommTotals values based on some particular grouping, please specify it. Or maybe you want the select statement to have a grand total of CommTotals specified for each row? Please provide sample values, actual results and expected results.

[demausdauth]

In my original post I had included the Group By from when I had tried to run the statement with a SUM() of the Case statement as had been previously suggested this is the original Select statement that I was trying to run:

SELECT     Agency, Type, CompanyNumber, AgencyDirect, PolicyNumber, Date, KeyTranCode, Time, UpdateTime, InstTime, CustomerNumber, PolicySeq, 
                      TranCode, UpdateDate, EffDate, LobCode, GrossPrem, SplitPrem, CurrComm, PrevComm, SplitComm, ProdSplit, PayableSelected, PayableFromInstall, 
                      BusinessType, AcctType, CommMarked, CommReconciled, BrokerNumber, CommSW, 
                      CASE AcctType WHEN 'C' THEN CASE AgencyDirect WHEN 'D' THEN CommMarked ELSE CurrComm END WHEN 'A' THEN (CurrComm - PrevComm) 
                      END AS CommTotals
FROM         Payables
WHERE     (PayableSelected = 1) AND (Type = 'C')
 

What i want to accomplish is a grand total of the CommTotals column.
ie:
CommTotals GrandTotals
40.00
34.00
-25.00
20.00
-100.00 -31.00

[almaz]

Still not sure if I understood you well. Can you try this one?

;WITH Source (Agency, Type, CompanyNumber, AgencyDirect, PolicyNumber, Date, KeyTranCode, Time, UpdateTime, InstTime, CustomerNumber, PolicySeq, 
                      TranCode, UpdateDate, EffDate, LobCode, GrossPrem, SplitPrem, CurrComm, PrevComm, SplitComm, ProdSplit, PayableSelected, PayableFromInstall, 
                      BusinessType, AcctType, CommMarked, CommReconciled, BrokerNumber, CommSW, CommTotals)
AS
(SELECT     Agency, Type, CompanyNumber, AgencyDirect, PolicyNumber, Date, KeyTranCode, Time, UpdateTime, InstTime, CustomerNumber, PolicySeq, 
                      TranCode, UpdateDate, EffDate, LobCode, GrossPrem, SplitPrem, CurrComm, PrevComm, SplitComm, ProdSplit, PayableSelected, PayableFromInstall, 
                      BusinessType, AcctType, CommMarked, CommReconciled, BrokerNumber, CommSW, 
                      CASE AcctType WHEN 'C' THEN CASE AgencyDirect WHEN 'D' THEN CommMarked ELSE CurrComm END WHEN 'A' THEN (CurrComm - PrevComm) 
                      END AS CommTotals
FROM         Payables
WHERE     (PayableSelected = 1) AND (Type = 'C')
)
select *, GrandTotal = (select sum(CommTotals) from Source) from Source

[demausdauth]

Awesome works perfectly! thanks almaz
we learn something new everyday