Search code examples
countsuminner-join

COUNT AND SUM WITH INNER JOIN

I have two tables that are linked on 4 fields DP_STOP has those 4 fields plus a customer id DP_ORDER has those 4 fields plus orders The Customer (LOCATION_ID) is on 4 Routes as Stops on different in the DP_STOP table The Customer had a total of 7 Orders for those 4 Routes When I try to count the number of Customers while SUM the Orders, the Count is the number of Orders instead of the number of Stops

DP_STOP table

REGION | ROUTE_ID | ROUTE_DATE | INTERNAL_STOP | LOCATION_ID

11600-A| 202      | 2018-11-01 | 9             | 00001
11600-A| 202      | 2018-11-08 | 9             | 00001
11600-A| 202      | 2018-11-15 | 9             | 00001
11600-A| 202      | 2018-11-22 | 9             | 00001

DP_ORDER table

REGION | ROUTE_ID | ROUTE_DATE | INTERNAL_STOP | ORDER_NUMBER | PLANNED_SIZE1

11600-A| 202      | 2018-11-01   9             | 1A           | 5
11600-A| 202      | 2018-11-08   9             | 2B           | 5
11600-A| 202      | 2018-11-08   9             | 2C           | 5
11600-A| 202      | 2018-11-15   9             | 3A           | 5
11600-A| 202      | 2018-11-15   9             | 3B           | 5
11600-A| 202      | 2018-11-22   9             | 4A           | 5
11600-A| 202      | 2018-11-22   9             | 4B           | 5

When I attempt to COUNT the number of stops while SUM of the Order, the COUNT is counting the number of Orders instead of the number of Stops with the following query:

SELECT  
COUNT (L.LOCATION_ID) AS DELIVERIES,
L.LOCATION_ID AS CUSTOMER_ID,
SUM (O.PLANNED_SIZE1) AS CASES
    FROM TSDBA.DP_STOP L
       INNER JOIN TSDBA.DP_ORDER O
       ON (O.REGION_ID=L.REGION_ID) 
       AND (O.ROUTE_DATE=L.ROUTE_DATE) 
       AND (O.ROUTE_ID=L.ROUTE_ID)
       AND (O.INTERNAL_STOP_ID=L.INTERNAL_STOP_ID)
  WHERE L.ROUTE_DATE BETWEEN '2018-11-01' AND '2018-11-28' 
  AND L.REGION_ID='11600-A'
    GROUP BY L.LOCATION_ID

My results from the query are:

DELIVERIES | CUSTOMER_ID | PLANNED_SIZE1
7          | 00001       | 35

I want it to be:

DELIVERIES | CUSTOMER_ID | PLANNED_SIZE1
4          | 00001       | 35
Solution

  • You should add distinct by expression which is uniquely defines a row for that, i.e. COUNT (distinct L.ROUTE_DATE) (in your case).