Insert query result into database row

Question

I have this table which is used to generate second table with the result:

INSERT INTO payment_transactions_daily_facts (count, volume, date, year, month, week, day, transaction_type, contract_id, merchant_id, terminal_id, status, card_brand, currency)
SELECT
        COUNT(*) count,
        SUM(amount) volume,
        DATE(created_at) date,
        YEAR(created_at) year,
        MONTH(created_at) month,
        WEEK(created_at) week,
        DAY(created_at) day,
        type transaction_type,
        contract_id, merchant_id, terminal_id,
        status, card_brand, currency
        FROM payment_transactions
        WHERE created_at BETWEEN '2018-11-11' AND '2018-11-14'
        GROUP by date, contract_id, merchant_id, terminal_id, transaction_type, status, card_brand, currency

When I execute only the select statement I get:

count   volume  date    year    month   week    day transaction_type    contract_id merchant_id terminal_id status  card_brand  currency
1   3000    2018-11-11  2018    11  45  11  Sale    1   2   1   approved    NULL    USD

But I can't execute the INSERT. I get error:

Field 'id' doesn't have a default value

I use for I sequence:

CREATE TABLE `payment_transactions_daily_facts` (
  `id` int(11) NOT NULL,
  `card_brand` varchar(255) DEFAULT NULL,
  `contract_id` int(11) DEFAULT NULL,
  `count` int(11) DEFAULT NULL,
  `currency` varchar(255) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `day` int(11) DEFAULT NULL,
  `merchant_id` int(11) DEFAULT NULL,
  `month` int(11) DEFAULT NULL,
  `status` varchar(255) DEFAULT NULL,
  `terminal_id` int(11) DEFAULT NULL,
  `transaction_type` varchar(255) DEFAULT NULL,
  `volume` int(11) DEFAULT NULL,
  `week` int(11) DEFAULT NULL,
  `year` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

How I can use sequence for id?

Answer 1

You need the AUTO_INCRMENT default for your id field, so:

ALTER TABLE `payment_transactions_daily_facts`
CHANGE COLUMN `id` `id` INT(11) NOT NULL AUTO_INCREMENT FIRST;

Then, next time you run your INSERT, the system will create an auto incremental id for you automatically.