Hello everyone,

I'm trying to put together a piece of code in SQL that is similar to the sumif function in Excel. Basically, what I am trying to do is add the total number of miles that a truck travels on a particular delivery and then categorize that load as "very long" if it is over 500 miles. The problem is that the truck might make multiple stops on a delivery and each line item is an individual stop.

To help explain, I threw together an example below. Basically what I want to do is add up the total delivery miles for each Load ID#. For instance, Load ID# 1 equals a total of 250 miles so it should be ignored. Load ID#2 however equals 1100 miles and should be considered "very Long". My overall goal is to be able to count the total # of "very long" loads.

I'd appreciate any help!



Stop # Load ID # Miles
1 1 100
2 1 50
3 1 100
1 2 300
2 2 400
3 2 400
1 3 10
2 3 15
3 3 20


(This is sort of what I was thinking?)


SELECT
Count(Distinct [Route #])AS Loads
FROM TBL_MasterData
Where
Sum(i.Distance) AS [iRaw_Miles]) >500
GROUP BY
Route #,
Distance

ORDER BY
Route #

If you sum up the miles grouped by load, this will give you the total you are looking for. If you want to further filter the resulting group by look at HAVING as follows: