Problem with SUM - Help please   

See DDL Below

Put simply, I need to get some numeric values in a SELECT statement
and then SUM them (I realise in the example below that I don't need to
SUM them, I can just + them).  The numeric values are stored in a
related table so I've created a scalar function to get the value.  The
first SELECT statement works fine; the second barfs reporting "Invalid
column name 'Expr1'" (as well as 'Expr2' and 'Expr3')

-- First SELECT statement
SELECT
	TestID,
	dbo.fnTEST(TestDDL1) AS Expr1,
	dbo.fnTEST(TestDDL2) AS Expr2,
	dbo.fnTEST(TestDDL3) AS Expr3,
	SUM(dbo.fnTEST(TestDDL1) + dbo.fnTEST(TestDDL2) + dbo.fnTEST
(TestDDL3)) AS Expr4
FROM
	dbo.EC_TEST
GROUP BY
	TestID,
	TestDDL1,
	TestDDL2,
	TestDDL3


-- Second SELECT statement
SELECT
	TestID,
	dbo.fnTEST(TestDDL1) AS Expr1,
	dbo.fnTEST(TestDDL2) AS Expr2,
	dbo.fnTEST(TestDDL3) AS Expr3,
	SUM(Expr1 + Expr2 + Expr3) AS Expr4
FROM
	dbo.EC_TEST
GROUP BY
	TestID,
	Expr1,
	Expr2,
	Expr3

========= DDL================

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EC_DDL](
	[DDLID] [int] IDENTITY(1,1) NOT NULL,
	[DDLValue] [nchar](10) NOT NULL,
	[DDLScore] [decimal](18, 2) NOT NULL,
 CONSTRAINT [PK_EC_DDL] PRIMARY KEY CLUSTERED
(
	[DDLID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EC_TEST](
	[TestID] [int] IDENTITY(1,1) NOT NULL,
	[TestDDL1] [int] NULL,
	[TestDDL2] [int] NULL,
	[TestDDL3] [int] NULL,
 CONSTRAINT [PK_EC_TEST] PRIMARY KEY CLUSTERED
(
	[TestID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE FUNCTION [dbo].[fnTEST]
	(
		-- Add the parameters for the function here
		@p1 int
	)
	RETURNS decimal(18,2)
	AS
	BEGIN
		-- Declare the return variable here
		DECLARE @Result decimal(18,2)

			SELECT
				@Result = IsNull(dbo.EC_DDL.DDLScore, 0)
			FROM
				dbo.EC_DDL
			WHERE
				dbo.EC_DDL.DDLID = @p1
		-- Return the result of the function
		RETURN @Result

END

date: Fri, 3 Jul 2009 03:15:49 -0700 (PDT)   author:   Edward

Re: Problem with SUM - Help please   

Hello Edward,

Just wrap another selct around it:

select 
TestID,
Expr1,
Expr2,
Expr3,
SUM(Expr1 + Expr2 + Expr3) AS Expr4
from
(
-- Second SELECT statement
SELECT
TestID,
dbo.fnTEST(TestDDL1) AS Expr1,
dbo.fnTEST(TestDDL2) AS Expr2,
dbo.fnTEST(TestDDL3) AS Expr3,
SUM(Expr1 + Expr2 + Expr3) AS Expr4
FROM
dbo.EC_TEST
) source
GROUP BY
TestID,
Expr1,
Expr2,
Expr3


> See DDL Below
> 
> Put simply, I need to get some numeric values in a SELECT statement
> and then SUM them (I realise in the example below that I don't need to
> SUM them, I can just + them).  The numeric values are stored in a
> related table so I've created a scalar function to get the value.  The
> first SELECT statement works fine; the second barfs reporting "Invalid
> column name 'Expr1'" (as well as 'Expr2' and 'Expr3')
> 
> ========= DDL================
> 
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE TABLE [dbo].[EC_DDL](
> [DDLID] [int] IDENTITY(1,1) NOT NULL,
> [DDLValue] [nchar](10) NOT NULL,
> [DDLScore] [decimal](18, 2) NOT NULL,
> CONSTRAINT [PK_EC_DDL] PRIMARY KEY CLUSTERED
> (
> [DDLID] ASC
> )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY
> = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] )
> ON [PRIMARY]
> 
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE TABLE [dbo].[EC_TEST](
> [TestID] [int] IDENTITY(1,1) NOT NULL,
> [TestDDL1] [int] NULL,
> [TestDDL2] [int] NULL,
> [TestDDL3] [int] NULL,
> CONSTRAINT [PK_EC_TEST] PRIMARY KEY CLUSTERED
> (
> [TestID] ASC
> )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY
> = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] )
> ON [PRIMARY]
> 
> CREATE FUNCTION [dbo].[fnTEST]
> (
> -- Add the parameters for the function here
> @p1 int
> )
> RETURNS decimal(18,2)
> AS
> BEGIN
> -- Declare the return variable here
> DECLARE @Result decimal(18,2)
> SELECT
> @Result = IsNull(dbo.EC_DDL.DDLScore, 0)
> FROM
> dbo.EC_DDL
> WHERE
> dbo.EC_DDL.DDLID = @p1
> -- Return the result of the function
> RETURN @Result
> END
> 
--
Jesse Houwing
jesse.houwing at sogeti.nl

date: Fri, 3 Jul 2009 11:33:39 +0000 (UTC)   author:   Jesse Houwing am

Re: Problem with SUM - Help please   

Another way to solve this is to use CROSS APPLY (SQL Server 2005/2008):

SELECT TestID,
        Expr1,
        Expr2,
        Expr3,
        SUM(Expr1 + Expr2 + Expr3) AS Expr4
FROM dbo.EC_TEST
CROSS APPLY (
SELECT dbo.fnTEST(TestDDL1) AS Expr1,
        dbo.fnTEST(TestDDL2) AS Expr2,
        dbo.fnTEST(TestDDL3) AS Expr3) AS C
GROUP BY TestID,
          Expr1,
          Expr2,
          Expr3;

-- 
Plamen Ratchev
http://www.SQLStudio.com

date: Fri, 03 Jul 2009 10:31:49 -0400   author:   Plamen Ratchev