Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2011

    Unanswered: Excel Sumif Equivalent in SQL?

    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?)

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

    Route #

  2. #2
    Join Date
    Sep 2009
    San Sebastian, Spain
    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:

    `Load ID #`, SUM(Miles)
    FROM TBL_MasterData
    GROUP BY `Load ID #`
    HAVING SUM(Miles) > 500
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts