SUM not working????

• mutlyp

  SSC Enthusiast

  Points: 103

  More actions

  October 20, 2010 at 9:23 am

  #241896

  RiderID_PK|StartDate |EndDate | RouteID_FK|Expr1|FriDate| Total|

  27 | 10/14/2004 |5/23/2010 |107|107|1/15/2010|28.8|

  29 | 10/14/2004|NULL | 107|107|1/15/2010|17.28|

  32 | 10/14/2004|3/14/2010 |107|107|1/15/2010|28.8|

  33 | 10/14/2004|NULL | 107|107|1/15/2010|23.04|

  35 | 10/14/2004|NULL | 107|107|1/15/2010|17.28|

  36 | 10/14/2004|5/2/2010 | 107|107|1/15/2010|28.8|

  780 | 7/8/2006 |NULL | 107|107|1/15/2010|28.8|

  1178 | 4/22/2007 |NULL | 107|107|1/15/2010|28.8|

  1272 | 7/8/2007 |NULL | 107|107|1/15/2010|28.8|

  1661 | 2/1/2009 | 5/2/2010 | 107|107|1/15/2010|28.8|

  1851 | 10/11/2009|NULL | 107|107|1/15/2010|28.8|

  1852 | 10/11/2009|9/19/2010 |107|107|1/15/2010|28.8|

  Those are the result I get when I run this query:

  SELECT Rider.RiderID_PK, Rider.StartDate, Rider.EndDate, Rider.RouteID_FK, Rider.RouteID_FK AS Expr1, RiderFareHistory.FriDate,

  SUM(RiderFareHistory.AmountPaid) AS Total

  FROM Rider INNER JOIN

  RiderFareHistory ON Rider.RiderID_PK = RiderFareHistory.RiderID_FK

  WHERE (RiderFareHistory.FriDate = CONVERT(DATETIME, '2010-01-15 00:00:00', 102)) AND (Rider.RouteID_FK = '107') AND

  (Rider.EndDate > CONVERT(DATETIME, '2010-01-15 00:00:00', 102) OR

  Rider.EndDate IS NULL) AND (Rider.StartDate < CONVERT(DATETIME, '2010-01-15 00:00:00', 102))

  GROUP BY Rider.RiderID_PK, Rider.RouteID_FK, RiderFareHistory.FriDate, Rider.EndDate, Rider.StartDate

  I need to SUM all the values in the AmountPaid Column.

  What am I doing wrong????

  Sorry for the formatting. I attatched a photo of the query and the results.

  Thank You

  MutlyP