# Sum Fares where the job date equals job date

• ### Question

• User1568384800 posted

I have this to retrieve a bunch of data. However, I've been trying to sum the Fares where job date is the same in a row. e.g. there are two 3rd July's one showing 1.50 and one showing 1.51 I want each row with those dates to show this summed e.g. 3.01.

```SELECT      JS.ScheduleStartDate AS JobDate, SD.JobNumber, SD.OutletPostcode, SD.JobName + ' ' + SD.CustomersRefNo + ', ' + SD.JobLocation AS JobDescription,
JS.AmPmNotes, SD.SiteRefNo, CAST(DATEDIFF(MINUTE, '0:00:00', ISNULL(T.TotalTimeAtTheJob, '0:00:00')) / 60 AS VARCHAR(8))
+ ':' + RIGHT('0' + CAST(DATEDIFF(MINUTE, '0:00:00', ISNULL(T.TotalTimeAtTheJob, '0:00:00')) % 60 AS VARCHAR(2)), 2) AS TimeAtTheJob,
CAST(SUM(DATEDIFF(MINUTE, '0:00:00', ISNULL(T.TravelTime, '0:00:00'))) / 60 AS VARCHAR(8)) + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(MINUTE, '0:00:00',
ISNULL(T.TravelTime, '0:00:00'))) % 60 AS VARCHAR(2)), 2) AS TravelTime, ISNULL(SUM(T.OtherFares), 0) AS Fares, ISNULL(T.Parking, 0) AS Parking
FROM        dbo.JobSchedule AS JS INNER JOIN
dbo.JobDetail AS JD ON JS.JobDetailID = JD.JobDetailID INNER JOIN
dbo.SiteDetail AS SD ON JD.SiteDetailID = SD.SiteDetailID LEFT OUTER JOIN
dbo.JobTiming AS T ON JD.JobDetailID = T.JobDetailID
WHERE       (JS.UserId = @UserID) AND (JS.ScheduleStartDate BETWEEN @FromDate AND @ToDate) AND (SD.SiteRefNo <> - 4)
GROUP BY	T.TravelTime, JS.ScheduleStartDate, JD.JobNumber, SD.JobNumber, SD.OutletPostcode, JD.JobLocation, SD.CustomersRefNo, SD.JobLocation, SD.JobName,
JS.AmPmNotes, SD.SiteRefNo, T.TotalTimeAtTheJob, T.Parking```

Much thanks for any help.

Monday, August 14, 2017 2:46 PM

### All replies

• User77042963 posted

Try to convert  JS.ScheduleStartDate from datetime to date in both select and group by clause:

` Cast(JS.ScheduleStartDate as date)`
Monday, August 14, 2017 4:00 PM
• User1568384800 posted

Unfortunately this makes no difference.

Tuesday, August 15, 2017 7:42 AM
• User77042963 posted

Without seeing your table DDL and sample data, it is not easy to find your issue. If you can mock up a sample to represent your issue, it may help you to get an answer.

Tuesday, August 15, 2017 1:21 PM
• User347430248 posted

Hi purplepint,

refer example below.

Table:

Query:

```SELECT date,sum(fare) AS Total
FROM
job_data
Group by date
Order by date asc```

Output:

Regards

Deepak

Thursday, September 21, 2017 7:39 AM