SELECT Orders.*, tot.TOTAMT ; > FROM Orders ; > JOIN (SELECT OrdNbr, SUM(AMT) AS TOTAMT FROM Orders GROUP BY OrdNbr) tot ; > ON tot.OrdNbr = Orders.OrdNbr > >>>In moving from SET EngineBehavior 70 to SET EngineBehavior 90 , I've encountered the same difficulties mentioned numerous times in this forum regarding the special attention necessary for the GROUP BY clause. I now understand the changes that are necessary when the old code fails, and have been happily making them as necessary.
SELECT *, SUM(AMT) AS TOTAMT FROM Orders GROUP BY OrdNbr>>The table "Orders", unfortunately, contains both parent records and child records. This particular selection would give me one record for each OrdNbr and all of the original fields from "Orders", so that I would, in effect, be creating the parent records (with the fields for what should have been child records containing irrelevant information.)