Insert query help

1

data exampleHi guys

I have the data below (table name current_table),

Current data
record department failed type date system Group fail number
1 803 data 16-Nov C M 0
1 803 data 16-Nov D M 0
1 803 data 16-Nov E M 0
1 803 network 16-Nov B M 0
1 803 network 16-Nov C M 0
1 803 network 16-Nov E M 0
1 803 network 16-Nov A M 1
1 803 data 16-Nov A M 1

I want to create a query to insert all the above data into the new table called test table, the new table should look ;like below.

record department date system Group fail number
1 803 16-Nov C M 0
1 803 16-Nov D M 0
1 803 16-Nov B M 0
1 803 16-Nov E M 0
1 803 16-Nov A M 2

when I try to do this some rows get deleted.

can anyone help me?

I have uploaded an imagine of both the data to make it clearer

2

Just do a GROUP BY of the Record, Department, Date, System, and Group columns with SUM on the Fail Number column.

BTW... storing dates like you have will cause you a heap of trouble further down the line. They should be stored in a DATETIME, DATETIME(2), or DATE datatype column.