Help modifying SUM

• dba321

  Hall of Fame

  Points: 3917

  More actions

  February 9, 2006 at 2:30 pm

  #152754

  Table Structure

  DCR

  ------

  CustomerNumber

  CC

  PDC

  EnteredDate

  FeeGoal

  FeeSchedule

  PostedAmount

  I need a query which will do:

  SUM(PostedAmount + SUM(All PDC where EnteredDate in current month) + SUM(All CC where entered date in current month) + SUM(All PDC where EnteredDate not in current month) + SUM(All CC where entered date in current month)) * FeeSchedule

  for each company

  call that In-House for the above Sum

  Then I need to do a grand total on all In-House as one sum.  I did something similar like this for another calculation but it didn't include the additions of PDC and CC.  That query looked like this:

  Select Total = SUM(gt)

  FROM

  (Select Distinct CustomerName, CustomerNumber, FeeGoal AS FG, FeeSchedule,

                  (cast(FeeGoal as numeric(30,10)) / FeeSchedule) * 100 AS gt

  from DCR WHERE branch='00002'

  group by CustomerName,

               CustomerNumber,

               FeeGoal,

               FeeSchedule

  ) as dTable

  so really what I want to change is (cast(FeeGoal as numeric(30,10)) / FeeSchedule) * 100  to add the PDC and CC sums and then * FeeSchedule instead of / like I have here

• [KH]

  SSCommitted

  Points: 1908

  More actions

  February 9, 2006 at 11:53 pm

  #620022

  Can you post your table DDL, some sample data and expected result ?

• Jesper-244176

  SSCertifiable

  Points: 7142

  More actions

  February 10, 2006 at 2:22 am

  #620040

  What's the point in calculating

  SUM(All PDC where EnteredDate in current month) + SUM(All PDC where EnteredDate not in current month)

  Isn't that simply SUM(All PDC)?

  You write "for each company", but I don't see company in your table. Perhaps you mean CustomerNumber?

• R. Brush

  SSCommitted

  Points: 1669

  More actions

  February 10, 2006 at 7:39 am

  #620128

  You did not include enough information for an actual solution to be presented. However, the following example shows you how to conditionally SUM a column's value, using a CASE statement:

  SELECT [CustomerNumber]

       , SUM(PostedAmount

          + SUM(CASE WHEN MONTH([EnteredDate])=MONTH(GETDATE()) THEN [PDC] ELSE 0 END)

          + SUM(CASE WHEN MONTH([EnteredDate])=MONTH(GETDATE()) THEN [CC] ELSE 0 END)

          AS Total

  I hope this helps.