CODEWITHSUNDEEP
sqldb2
Edward Rhoades
Edward Rhoades

Reputation: 31

DB2 I need to combine two queries and both are grouping by the same column

I am having trouble combining these two queries. 

SELECT  d1.DRIVER_ID, SUM(LOADED_MILES)+SUM(EMPTY_MILES) AS Miles
FROM DRIVERPAY 
    WHERE LEG_DATE >= '2013-10-05'
AND LEG_DATE < '2013-12-29'
AND PAY_DESCRIPTION = 'Paid by Mileage'
AND PMT_STATE = 'X'
GROUP BY d1.DRIVER_ID

SELECT  DRIVER_ID, SUM(HOURS) 
FROM DRIVERPAY 
WHERE LEG_DATE >= '2013-10-05'
AND LEG_DATE < '2013-12-29' 
AND PAY_CODE = 'P_HOURLY' 
AND PAID = 'True' 
GROUP BY DRIVER_ID

I have tried to use a full outer join but I would like the Driver_ID in one column not two. 

SELECT DriverMiles.Driver_ID, DriverHours.Driver_ID, DriverMiles.Miles,
DriverHours.Hours
FROM
(
SELECT  d1.DRIVER_ID, SUM(LOADED_MILES)+SUM(EMPTY_MILES) AS Miles
FROM DRIVERPAY AS D1
WHERE LEG_DATE >= '2013-10-05'
AND LEG_DATE < '2013-12-29'
AND PAY_DESCRIPTION = 'Paid by Mileage'
AND PMT_STATE = 'X'
AND  PAID = 'True'
GROUP BY D1.DRIVER_ID
) AS DriverMiles
FULL OUTER JOIN
(
SELECT  d2.DRIVER_ID, SUM(HOURS) AS Hours
FROM DRIVERPAY AS d2
WHERE LEG_DATE >= '2013-10-05'
AND LEG_DATE < '2013-12-29'
AND PAY_CODE = 'P_HOURLY'
AND PAID = 'True'
GROUP BY d2.DRIVER_ID
) as DriverHours
on DriverMiles.Driver_ID = DriverHours.Driver_ID

I've also tried a CTE but it doesn't work either. Does anyone have any suggestions?

Upvotes: 0

Views: 49

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269663

You are querying on the same table and aggregating by the same column. That means that you can use conditional aggregation:

SELECT  d1.DRIVER_ID,
        SUM(CASE WHEN PAY_DESCRIPTION = 'Paid by Mileage'
                 THEN COALESCE(LOADED_MILES, 0) + COALESCE(EMPTY_MILES, 0)
                 ELSE 0
             END) AS Miles,
        SUM(CASE WHEN PAY_CODE = 'P_HOURLY' THEN HOURS ELSE 0 END) 
FROM DRIVERPAY d1
WHERE LEG_DATE >= '2013-10-05' AND
      LEG_DATE < '2013-12-29' AND
      ((PAY_DESCRIPTION = 'Paid by Mileage' AND PMT_STATE = 'X') OR
       (PAY_CODE = 'P_HOURLY' AND PAID = 'True' )
      )
GROUP BY d1.DRIVER_ID

Upvotes: 3

Related Questions