SQL Sum() Function and Join

Question

TWO TABLES

ORDER_TABLE:

ORDER_ID | TARGET_QTY | 
-----------------------
1        |    50     
2        |    100  
3        |    200

ALLOC_TABLE:

ORDER_ID | TARGET_QTY |
-----------------------
1        |         50  
2        |         20  
2        |         30  
3        |        200

I want to return ORDER_TABLE.ORDER_ID's where the SUM(ALLOC_TABLE.TARGET_QTY) for an order ID is < ORDER_TABLE.TARGET_QTY. Ex. of desired output below:

ORDER_ID | 
---------
2

All help is appreciated!

DVT · Accepted Answer · 2016-12-06 00:21:02Z

2

SELECT
at.ORDER_ID
FROM
    ALLOC_TABLE at
    JOIN ORDER_TABLE ot ON at.ORDER_ID = ot.ORDER_ID
GROUP BY
    at.ORDER_ID, ot.TARGET_QTY
HAVING
    SUM(at.TARGET_QTY) < ot.TARGET_QTY

answered Dec 6, 2016 at 0:21

DVT

3,1274 gold badges16 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Atie · Accepted Answer · 2016-12-06 06:25:15Z

0

try this pls

SELECT  at.ORDER_ID
FROM   ALLOC_TABLE at
full outer JOIN ORDER_TABLE ot ON at.ORDER_ID = ot.ORDER_ID
GROUP BY at.ORDER_ID
HAVING SUM(at.TARGET_QTY) < ot.TARGET_QTY

answered Dec 6, 2016 at 6:25

Atie

113 bronze badges

Collectives™ on Stack Overflow

SQL Sum() Function and Join

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related