CODEWITHSUNDEEP
sql
Matt
Matt

Reputation: 379

Sum up multiple values based off a single column

For context, I work in transportation. Also, I apologize for a poor title - I'm not exactly sure how to summarize my issue.

I am currently editing an existing report which returns a drivers ID, their name, when they were hired, and the total amount of miles they have driven since they have started at the company. It was brought to my attention that drivers who move within the company are assigned a different driverID, which is not counted towards their total miles driven. Using an example provided to me, I was indeed able to confirm this scenario, as indicated below:

DriverCode   DriverName
-----------  ----------------
WETDE        Wethington,Dean
WETDEA       Wethington,Dean

This is the query that gets the above (example driver is hardcoded at the moment):

select mpp.mpp_id as DriverCode,
       mpp.mpp_lastfirst as DriverName
from manpowerprofile mpp
    outer apply (select top 1 mpp_id
                 from manpowerprofile) as id
where mpp_firstname = 'Dean'
    and mpp_lastname = 'Wethington'

This is the current query as it stands:

SELECT lh.lgh_driver1 as DriverCode
              ,m.mpp_lastfirst as DriverName
              ,m.mpp_hiredate as HireDate
              ,SUM(s.stp_lgh_mileage) as TotMiles
FROM stops s (nolock)
             INNER JOIN legheader lh (nolock) on lh.lgh_number = s.lgh_number
             INNER JOIN manpowerprofile m (nolock) on m.mpp_id = lh.lgh_driver1

            /* OUTER APPLY ( SELECT top 1 mpp_id
                           FROM manpowerprofile) as id */

WHERE m.mpp_terminationdt > GETDATE()
              AND m.mpp_id <> 'UNKNOWN'
              AND lh.lgh_outstatus = 'CMP'
GROUP BY lh.lgh_driver1, m.mpp_lastfirst, m.mpp_hiredate
    HAVING SUM(s.stp_lgh_mileage) > 850000
ORDER BY DriverCode DESC

What I'm looking to do is check to see if a name exists twice, and if it does, add both of those driver code's total miles together to return a single result for that individual driver. I'm a pretty novice SQL Developer still and have only now really started to delve into databases.

My current train of thought was to use an outer apply, but I'm sure there's a better way to do this.

Upvotes: 0

Views: 30

Answers (1)

MatBailie
MatBailie

Reputation: 86716

As per your comment, leaving off the driver code and hire date... (Because they could/would be different for the drivers being combined.)

SELECT 
    m.mpp_lastfirst as DriverName
   ,SUM(s.stp_lgh_mileage) as TotMiles
FROM
    stops s (nolock)
INNER JOIN
    legheader lh (nolock)
        on lh.lgh_number = s.lgh_number
INNER JOIN
    manpowerprofile m (nolock)
        on m.mpp_id = lh.lgh_driver1
WHERE
        m.mpp_terminationdt > GETDATE()
    AND m.mpp_id <> 'UNKNOWN'
    AND lh.lgh_outstatus = 'CMP'
GROUP BY
    m.mpp_lastfirst
HAVING
    SUM(s.stp_lgh_mileage) > 850000
ORDER BY
    m.mpp_lastfirstDESC

Upvotes: 1

Related Questions