How to use SUM function based on a parameter

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to use SUM function based on a parameter

Author

Topic

skiabox
Posting Yak Master

169 Posts

Posted - 2009-09-11 : 06:52:04

I have a parameter column1 with values (0, 30 or 60).
The other columns have month names from January to December.
I want to create a total row that do a summary based on the parameters.
Here is an example :
Under the July column I sum the value of July if column1 is 0, June value if column1 is 30 and May value if column1 is 60.
Is it possible?
Thank you very much!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-09-11 : 07:15:13

SELECT SUM(CASE WHEN Column1 = 0 THEN July
WHEN Column1 = 30 THEN June
WHEN Column1 = 60 THEN May
ELSE 0
END)

From yourTable

Jim

Everyday I learn something that somebody else already knew

skiabox
Posting Yak Master

169 Posts

Posted - 2009-09-11 : 07:50:42

This is the whole code that I am using.
BGT_MONTH is an integer from 1 to 12 :

CREATE PROCEDURE [dbo].[GetTotalBudgetAmountsWithParam]
-- single parameter
@finYear INT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT x.BGC_CODE, x.BGC_DESC_GR, x.BGC_LEVEL, x.BGC_IS_LEAF, y.BGT_YEAR, y.BGT_MONTH, y.Expr1 AS BGT_AMOUNT, x.BGC_DESC_EN
FROM (SELECT BGC_ID, BGC_CODE, BGC_PARENT_CODE, BGC_DESC_GR, BGC_DESC_EN, BGC_LEVEL, BGC_IS_LEAF
FROM BUDGET_CODES) AS x LEFT OUTER JOIN
(SELECT BGT_YEAR, BGT_MONTH, BGT_BGC_CODE, SUM(Multi) AS Expr1
FROM
(SELECT a.BGT_YEAR, a.BGT_MONTH, a.BGT_BGC_CODE, (a.BGT_AMOUNT * (1 + b.CFL_VAT_PERCENTAGE) - a.BGT_AMOUNT * b.CFL_WTAX_PERCENTAGE) AS Multi, a.BGT_FINANCIAL_YEAR
FROM BUDGET a
LEFT OUTER JOIN CASH_FLOW b
ON a.BGT_BGC_CODE = b.CFL_BGC_CODE) AS t

WHERE (BGT_FINANCIAL_YEAR = @finYear)
GROUP BY BGT_BGC_CODE, BGT_YEAR, BGT_MONTH) AS y ON x.BGC_CODE = y.BGT_BGC_CODE

How can I insert the above statemeny here?
Thank you very much for helping!

PS : (The parameter is taken from CASH_FLOW.CFL_PAYMENT_DAYS column)

skiabox
Posting Yak Master

169 Posts

Posted - 2009-09-11 : 20:17:56

Difficult ?
:)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-13 : 14:05:27

didnt understand why you aggregate june,may values etc to july column. can you explian with example?

skiabox
Posting Yak Master

169 Posts

Posted - 2009-09-14 : 03:34:43

visakh16 : Do you have an email where I can send you an excel file?
Thank you very much.