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

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


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

    ORDER BY
    Route #

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

    Code:
    SELECT
    `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)
    http://www.it-iss.com
    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
  •