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

31
January 13, 2019, at 06:20 AM

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...

Answer 1

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 ...
READ ALSO
How to mirror mysql database traffic to another server

How to mirror mysql database traffic to another server

I have two server Mysql database in Cent OS 7Server A(192

32
How to calculate time diffrence in mysql

How to calculate time diffrence in mysql

I am storing the user activity in the table by start and end time , now i want to calculate how much overall time a user spends under each subject

30
Supress information in mysql prompt

Supress information in mysql prompt

I ran the query in mysql prompt , I need to supress the "1 row in set (000 sec)" in output prompt

26