Error when separately summing two columns

Question

I have two tables, SHPORD and CUSTORD, and I want to get the sum of uncompleted units for all orders that have not yet been fulfilled through today. Example at bottom.

Separate queries look like this:

SELECT SHPORD.ITEM, SUM(ISNULL(SHPORD.QTY_OPEN,0)) AS SHPSUM
FROM SHPORD SHPORD
WHERE SHPORD.DATE_DUE <= GETDATE() AND SHPORD.QTY_OPEN > 0
GROUP BY SHPORD.ITEM

SELECT CUSTORD.ITEM, SUM(ISNULL(CUSTORD.QTY_OPEN,0)) AS CUSTSUM
FROM CUSTORD CUSTORD
WHERE CUSTORD.DATE_DUE <= GETDATE() AND CUSTORD.QTY_OPEN > 0
GROUP BY CUSTORD.ITEM

These return the desired results.

But if I try to combine them like so:

SELECT CUSTORD.ITEM, SUM(ISNULL(CUSTORD.QTY_OPEN,0)) AS CUSTSUM, SUM(ISNULL(SHPORD.QTY_OPEN,0)) AS SHPSUM

FROM  CUSTORD CUSTORD

LEFT JOIN SHPORD SHPORD ON CUSTORD.ITEM = SHPORD.ITEM
AND SHPORD.DATE_DUE <= GETDATE() AND SHPORD.QTY_OPEN > 0

WHERE CUSTORD.DATE_DUE <= GETDATE() AND CUSTORD.QTY_OPEN > 0

GROUP BY CUSTORD.ITEM

I get the wrong results for about a third of the items.

This query has been deconstructed to be as simple as possible and the issue only occurs when both SUM columns are used. I'm at a loss. Probably just missing something simple.

What query should I be using?

Examples:

          Table CUSTORD
    CO#    ITEM     QTY OPEN
    111     A          5
    222     B          10
    333     A          15

          Table SHPORD
    SO#    ITEM     QTY OPEN
    444     B          2
    555     B          4
    666     A          7

             OUPUT
    ITEM    CUSTSUM    SHPSUM
    A          20         6
    B          10         7

score 0 · Accepted Answer · answered Nov 03 '15 at 19:18

0

select t1.ITEM,t1.CUSTSUM,t2.SHPSUM from
(SELECT ITEM, SUM(QTY_OPEN) AS CUSTSUM
FROM CUSTORD GROUP BY CUSTORD.ITEM) t1,
(SELECT ITEM, SUM(QTY_OPEN) AS SHPSUM 
FROM SHPORD GROUP BY SHPORD.ITEM) t2
where t1.ITEM=t2.ITEM

I hope this should work, i have not tested it but just try and see

answered Nov 03 '15 at 19:18

Vasanth Nag K V

4,860
5
24
48

Of course, if I have two working queries.... I should just use them as subqueries. Thank you! This seems to have worked. Any idea why combining them would fail? It had to be something in the syntax, I mean MS Query has to support having two separate sum columns, right? – mmak Nov 04 '15 at 14:31
sql will of course support two seperate sum columns but to answer your doubt in the above comment i will want to know the wrong output which you are getting with your query. (if the answer i gave you is working, please accept it :)) – Vasanth Nag K V Nov 04 '15 at 14:55

Error when separately summing two columns

1 Answers1