SQL เข้าร่วมกับผลรวม

ตามที่คุณกล่าวไว้ หากค่าที่เหลือทั้งหมดเหมือนกัน วิธีแก้ไขก็ง่ายมาก:

SELECT CATALOGID, 
       Sum(NUMITEMS), 
       ALLITEMS - NUMITEMS ignoreditems 
FROM   (SELECT CATALOGID, 
               NUMITEMS, 
               ALLITEMS - NUMITEMS ignoreditems 
        FROM   (SELECT i.CATALOGID, 
                       CASE 
                         WHEN EXISTS(SELECT PAYMENTODATE 
                                     FROM   PAYENTMETHODVALIDATION q 
                                     WHERE  q.PAYMENTNOODATE = o.OCARDTYPE) 
                              AND NOT EXISTS(SELECT BOOKED 
                                             FROM   BOOKEDORDERSIDS m 
                                             WHERE  Cast(m.BOOKED AS 
                                                    INT) = o.ORDERID) 
                       THEN 
                         Sum(i.NUMITEMS) 
                         WHEN EXISTS(SELECT PAYMENTODATE 
                                     FROM   PAYENTMETHODVALIDATION q 
                                     WHERE  q.PAYMENTODATE = o.OCARDTYPE) 
                              AND ODATE IS NOT NULL 
                              AND NOT EXISTS(SELECT BOOKED 
                                             FROM   BOOKEDORDERSIDS m 
                                             WHERE  Cast(m.BOOKED AS 
                                                    INT) = o.ORDERID) 
                       THEN 
                         Sum(i.NUMITEMS) 
                         ELSE 0 
                       END           numitems, 
                       Sum(NUMITEMS) allitems 
                FROM   ORDERS o 
                       INNER JOIN OITEMS i 
                               ON i.ORDERID = o.ORDERID 
                       INNER JOIN PRODUCTS T1 
                               ON T1.CATALOGID = i.CATALOGID 
                GROUP  BY i.CATALOGID, 
                          OCARDTYPE, 
                          ODATE, 
                          o.ORDERID) A) B 
       INNER JOIN (SELECT CATALOGID, 
                          ProcessedSucssessfully = Stuff( 
                          (SELECT ', ' + Cast( b.ORDERID 
                                  AS VARCHAR 
                                  (10)) 
                           FROM   OITEMS b 
                                  JOIN ORDERS o 
                                    ON 
                          b.ORDERID = o.ORDERID 
                           WHERE  b.CATALOGID = 
                                  a.CATALOGID 
                                  AND NOT 
                          EXISTS(SELECT BOOKED 
                                 FROM   BOOKEDORDERSIDS 
                                        m 
                                 WHERE 
                          Cast( 
                                m.BOOKED AS 
                          INT) = o.ORDERID) 
                                  AND ( EXISTS 
                                  (SELECT PAYMENTODATE 
                                   FROM 
                                        PAYENTMETHODVALIDATION q 
                                               WHERE 
                                  q.PAYMENTNOODATE = 
                                  o.OCARDTYPE) 
                                         OR EXISTS 
                                        (SELECT 
                                            PAYMENTODATE 
                                                   FROM 
                                            PAYENTMETHODVALIDATION 
                                            q 
                                                   WHERE 
                                        q.PAYMENTODATE = 
                  o.OCARDTYPE) 
                  AND o.ODATE IS NOT NULL ) 
                  FOR XML PATH('')), 1, 2, ''), 
                  NotProcessed = Stuff((SELECT ', ' + Cast( c.ORDERID AS VARCHAR 
                                               (10)) 
                  FROM   OITEMS c 
                  JOIN ORDERS o 
                  ON c.ORDERID = o.ORDERID 
                  WHERE  c.CATALOGID = a.CATALOGID 
                  AND ( o.OCARDTYPE IN ( 'mastercard' ) 
                  OR o.OCARDTYPE IS NULL ) 
                  AND o.ODATE IS NULL 
                  FOR XML PATH('')), 1, 2, '') 
                   FROM   OITEMS a 
                   GROUP  BY a.CATALOGID)C 
               ON B.CATALOGID = C.CATALOGID 
GROUP  BY CATALOGID, 
          ALLITEMS - NUMITEMS