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.
cipriano555Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jogosCommented:
AS DA2  does not exist outside the () so the where must be there for not having that error.

 
0
cipriano555Author Commented:
I did not understand that sentence.  Can you tell me what to do to correct my query?

Thanks

Cipriano
0
jogosCommented:
 ;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
   
  )

Open in new window

0
jogosCommented:
But could be faster if you make it a join
 ;WITH CTE_DIST AS 
  (
  SELECT ORDINAL, PDF 
  FROM dbo.DistFunc 
  WHERE PDF_ID = 2
  ) ,
 CTE_ACCUMULATIONS AS
 (
  SELECT DA1.[Ordinal] 
  , SUM(DA2.[PDF]) as [CDF]
  FROM CTE_DIST AS DA1
 left join CTE_DIST AS DA2 
    on DA2.[Ordinal] <= DA1.[Ordinal] 
   group by  DA1.[Ordinal] 
  )
  ) 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cipriano555Author Commented:
Wow, thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.