Link to home
Start Free TrialLog in
Avatar of seaero
seaero

asked on

On Duplicate Key Insert using aggregate value

How can I use the value of the sum() function in the following query in the calculation for the update in the ON DUPLICATE KEY UPDATE section of my query.  Please help!!!

INSERT INTO TEMP_INFO (PN,QTY_SOLD,MONTH,YEAR) SELECT PN,SUM(QTY_INVOICED) AS TOTAL_QTY_SOLD,MONTH(SHIP_DATE),YEAR(SHIP_DATE) from SALES_TABLE GROUP BY MONTH,YEAR,PN ORDER BY PN,YEAR,MONTH ASC ON DUPLICATE KEY UPDATE TEMP_INFO.QTY_SOLD = (TEMP_INFO.QTY_SOLD + TOTAL_QTY_SOLD)
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

please try this:
INSERT INTO TEMP_INFO (PN,QTY_SOLD,MONTH,YEAR) 
SELECT PN,SUM(QTY_INVOICED) AS TOTAL_QTY_SOLD,MONTH(SHIP_DATE),YEAR(SHIP_DATE) 
FROM SALES_TABLE 
GROUP BY MONTH,YEAR,PN 
ON DUPLICATE KEY UPDATE SET QTY_SOLD = QTY_SOLD + VALUES(TOTAL_QTY_SOLD)

Open in new window

Avatar of seaero
seaero

ASKER

No luck. I get the following error: Unknown column 'CALC_QTY_SOLD' in 'field list'
Avatar of seaero

ASKER

Oops, I mean 'TOTAL_QTY_SOLD'
SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of seaero

ASKER

Thanks for your help! I awarded ee rlee most of the points because his version worked. Thanks again!
Avatar of seaero

ASKER

Fantastic! That worked! Thanks for your help!
you're welcome ;)