cipriano555
asked on
Running Totals and CTE with Sql 2008
I am trying to compute running totals. I have a table called DistFunc
CREATE TABLE [dbo].[DistFunc](
[ORDINAL] [int] NOT NULL,
[PDF_ID] [int] NULL,
[PDF] [float] NULL,
[CDF] [float] NULL,
) ON [PRIMARY]
I only want to do the running totals for rows where PF_ID = 2
I created one CTE to remove the PF_ID field. The second CTE is supposed to do the running totals. The first CTE seems to be fine, the second one generates an error message.
Msg 4104, Level 16, State 1, Line 24
The multi-part identifier "DA2.Ordinal" could not be bound.
Here is the code that I have, which will not run.
;WITH CTE_DIST AS
(
SELECT ORDINAL, PDF
FROM dbo.DistFunc
WHERE PDF_ID = 2
) ,
CTE_ACCUMULATIONS AS
(
SELECT DA1.[Ordinal]
,(SELECT SUM(DA2.[PDF]) FROM CTE_DIST AS DA2) as [CDF]
FROM CTE_DIST AS DA1
WHERE DA2.[Ordinal] <= DA1.[Ordinal]
)
I have tried numerous variations on this, nothing has worked.
CREATE TABLE [dbo].[DistFunc](
[ORDINAL] [int] NOT NULL,
[PDF_ID] [int] NULL,
[PDF] [float] NULL,
[CDF] [float] NULL,
) ON [PRIMARY]
I only want to do the running totals for rows where PF_ID = 2
I created one CTE to remove the PF_ID field. The second CTE is supposed to do the running totals. The first CTE seems to be fine, the second one generates an error message.
Msg 4104, Level 16, State 1, Line 24
The multi-part identifier "DA2.Ordinal" could not be bound.
Here is the code that I have, which will not run.
;WITH CTE_DIST AS
(
SELECT ORDINAL, PDF
FROM dbo.DistFunc
WHERE PDF_ID = 2
) ,
CTE_ACCUMULATIONS AS
(
SELECT DA1.[Ordinal]
,(SELECT SUM(DA2.[PDF]) FROM CTE_DIST AS DA2) as [CDF]
FROM CTE_DIST AS DA1
WHERE DA2.[Ordinal] <= DA1.[Ordinal]
)
I have tried numerous variations on this, nothing has worked.
AS DA2 does not exist outside the () so the where must be there for not having that error.
ASKER
I did not understand that sentence. Can you tell me what to do to correct my query?
Thanks
Cipriano
Thanks
Cipriano
;WITH CTE_DIST AS
(
SELECT ORDINAL, PDF
FROM dbo.DistFunc
WHERE PDF_ID = 2
) ,
CTE_ACCUMULATIONS AS
(
SELECT DA1.[Ordinal]
,(SELECT SUM(DA2.[PDF]) FROM CTE_DIST AS DA2
WHERE DA2.[Ordinal] <= DA1.[Ordinal]
) as [CDF]
FROM CTE_DIST AS DA1
)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Wow, thanks!