SELECT cItemNo, cWareHouse, SUM(nReturned) AS nReturned, SUM(nShipped) AS nShipped FROM SELECT citemno1 AS cItemNo, cWhouse1 AS cWareHouse, SUM(ntrfqty1) AS nReturned, SUM(0000000000) AS nShipped FROM icitrf WHERE (dtrs BETWEEN '03/18/04' AND '03/25/04') AND (cwhouse1 BETWEEN 'X0000' AND 'X9999') GROUP BY 1, 2 UNION SELECT citemno2 AS cItemNo, cWhouse2 AS cWareHouse, SUM(0000000000) AS nReturned, SUM(ntrfqty2) AS nShipped FROM icitrf WHERE (dtrs BETWEEN '03/18/04' AND '03/25/04') AND (cwhouse2 BETWEEN 'X0000' AND 'X9999') GROUP BY 1, 2) dt GROUP BY 1, 2>I have a UNION with different souce names for my grouped fields, so SQL is complaining.
>SELECT citemno1 AS cItemNo, cWhouse1 AS cWareHouse, SUM(ntrfqty1) AS nReturned, 0000000000 AS nShipped >FROM icitrf >WHERE (dtrs BETWEEN '03/18/04' AND '03/25/04') AND (cwhouse1 BETWEEN 'X0000' AND 'X9999') >UNION >SELECT citemno2 AS cItemNo, cWhouse2 AS cWareHouse, 0000000000 AS nReturned, SUM(ntrfqty2) AS nShipped >FROM icitrf >WHERE (dtrs BETWEEN '03/18/04' AND '03/25/04') AND (cwhouse2 BETWEEN 'X0000' AND 'X9999') >GROUP BY 1, 2 >>