Oracle SQL: Use sequence in insert with Select Statement

Question

Basically I want to run the following query:

INSERT INTO historical_car_stats (historical_car_stats_id, year, month, make, model, region, avg_msrp, count)   SELECT     my_seq.nextval,     '2010',     '12',     'ALL',     'ALL',     region,     sum(avg_msrp * count) / sum(count),     sum(count)   FROM historical_car_stats   WHERE year = '2010'         AND month = '12'         AND make != 'ALL'   GROUP BY region; 

It doesn't work because "sequence number not allowed here" SQL error. How can I write this so Oracle will let me do what I want to do?

Answer 1

Assuming that you want to group the data before you generate the key with the sequence, it sounds like you want something like

INSERT INTO HISTORICAL_CAR_STATS (     HISTORICAL_CAR_STATS_ID,      YEAR,     MONTH,      MAKE,     MODEL,     REGION,     AVG_MSRP,     CNT)  SELECT MY_SEQ.nextval,        year,        month,        make,        model,        region,        avg_msrp,        cnt   FROM (SELECT '2010' year,                '12' month,                'ALL' make,                'ALL' model,                REGION,                sum(AVG_MSRP*COUNT)/sum(COUNT) avg_msrp,                sum(cnt) cnt           FROM HISTORICAL_CAR_STATS          WHERE YEAR = '2010'             AND MONTH = '12'            AND MAKE != 'ALL'           GROUP BY REGION)