Column count doesn't match value count at row 1 in MySQL

Question

I have created a table with the following query:

CREATE TABLE `dailydata` (
  `id`         int(9)         NOT NULL,
  `Date`       varchar(100)   NOT NULL,
  `EmpID`      varchar(100)   NOT NULL,
  `name`       varchar(100)   NOT NULL,
  `TeamName`   varchar(100)   NOT NULL,
  `active`     varchar(100)   NOT NULL,
  `idle`       double         NOT NULL,
  `Stime`      double         NOT NULL,
  `Etime`      varchar(100)   NOT NULL,
  `Inofficehr` decimal(20,2)  NOT NULL,
  `activehr`   double(19,2)   NOT NULL,
  `idlehr`     double(19,2)   NOT NULL,
  `Timestamp`  timestamp      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE 
   CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Set Primary key For 'id' column:

ALTER TABLE `dailydata`ADD PRIMARY KEY (`id`);

Now I'm trying to insert the data with this code:

INSERT INTO dailydata 
SELECT A.Date, A.EmpID, B.name, B.TeamName, 
SUM(CASE WHEN State = 'active' THEN A.MinutesatState ELSE 0 END) AS active,
SUM(CASE WHEN State = 'idle' THEN A.MinutesatState ELSE 0 END) AS idle,
Min(A.Statestarttime) AS Stime, MAX(A.StateEndtime) AS Etime,
ROUND((TIME_TO_SEC(MAX(A.StateEndtime))/60 - 
TIME_TO_SEC(MIN(A.StateStarttime))/60)/60,2) as Inofficehr, 
ROUND(SUM(CASE WHEN State = 'active' THEN A.MinutesatState ELSE 0 END)/60,2) 
AS activehr, 
ROUND(SUM(CASE WHEN State = 'idle' THEN A.MinutesatState ELSE 0 END)/60,2) 
AS idlehr 
FROM time A join ttld.login B on A.EmpID=B.username 
WHERE A.Date='01-01-2019' AND A.Statestarttime <>'' AND A.StateEndtime <>'' 
GROUP BY A.EmpID;

But I'm getting this error:

#1136 - Column count doesn't match value count at row 1

I understand I have not define the ID in my insert code and the reason for it is I want it to auto populate.

How can I modify my INSERT query code to do so...

Add AUTO_INCREMENT to your id column and include the primary key definition in CREATE TABLE or assign a value to id in your INSERT statement. — digijay
ALTER TABLE dailydata ADD PRIMARY KEY (id); will this not work do i need to create new table? this method working in other table i want to understand why this is not working in this one. — yatendra singh ranawat

Nick Nick · Accepted Answer · 2019-01-12T06:59:10

You have 13 columns in your table, but only 11 in your select, which is why you are getting this error.

But first, to make your id column auto-populate, you need to declare it as AUTO_INCREMENT. You can change it with an ALTER TABLE command:

ALTER TABLE dailydata CHANGE id id INT AUTO_INCREMENT PRIMARY KEY

Since you have a default value for your Timestamp column you don't need to insert a value for that.

To resolve the error you are seeing, you need to modify your INSERT query to to match the number of columns being inserted to the data either by
(a) naming the columns you are inserting; or
(b) inserting NULL values for the columns with no values (id and Timestamp)

e.g. (a)

INSERT INTO dailydata (`Date`, `EmpID`, `name`, `TeamName`, `active`, `idle` ,`Stime`, `Etime`, `Inofficehr`, `activehr`, `idlehr`)
SELECT A.Date, A.EmpID, B.name, B.TeamName, 
...

e.g. (b)

INSERT INTO dailydata
SELECT NULL AS id,
A.Date, A.EmpID, B.name, B.TeamName,
...
NULL AS Timestamp
FROM ...

Column count doesn't match value count at row 1 in MySQL

1 Answers