Try this:
INSERT INTO TempTable1STOCKDETAILS (PRODUCT_NO,PRODUCT_NAME,QTY_IN,UNIT_COST,FROM_DATE,TO_DATE)
SELECT a.PRODUCT_NO, a.PRODUCT_NAME, SUM(A.QTY_IN) AS QTY_IN, AVG(A.UNIT_COST) AS UNIT_COST, '29-09-2014' AS FROM_DATE, '30-09-2014' AS TO_DATE
FROM STOCKDETAILS A WHERE a.WH_NO='WH1' AND a.STOCK_JR = 'IN' AND a.STOCK_DATE BETWEEN '29-09-2014' AND '30-09-2014'
GROUP BY a.PRODUCT_NO, a.PRODUCT_NAME