howeroc howeroc - 6 months ago 13
SQL Question

I select some results with 'GROUP BY' ,but the results can not be inserted into my table and report 1064 error

I select some results with "GROUP BY" ,but the results can not be inserted into my table and report 1064 error

The “group by” results was total correct and the values was corresponding to the columns that i want to insert. But when I try the insert SQL on SQLyog it always show the 1064 error.

This is the error MySQL shows

sql query is:

INSERT INTO `admin_sales_list_count` (
`entity_id`,
`sku`,
`comment_count`,
`shaidan_count`,
`sales_count`,
`order_count`,
`update_time`
)
VALUES
SELECT
`product_id` AS `entity_id`,
`sku` AS `sku`,
SUM(`count_record`.`comment_count`) AS `comment_count`,
SUM(`count_record`.`shaidan_count`) AS `shaidan_count`,
SUM(`count_record`.`sales_count`) AS `sales_count`,
SUM(
`count_record`.`comment_count` + `count_record`.`sales_count`
) AS `order_count`,
NOW() AS `update_time`
FROM
(SELECT
`wsoi`.`product_id`,
`wsoi`.`sku`,
0 AS `comment_count`,
0 AS `shaidan_count`,
`wsoi`.`total_qty` AS `sales_count`
FROM
`rder_item` AS `wsoi`
UNION
ALL
SELECT
`acc`.`entity_id` AS `product_id`,
`acc`.`sku`,
`acc`.`comment_count`,
`acc`.`shaidan_count`,
0 AS `sales_count`
FROM
`admin_comment_count` AS `acc`) AS `count_record`
GROUP BY `count_record`.`product_id`

Answer

when you are using insert into from a select statement, there is no need to specify the VALUES keyword.. Try the following

insert into `admin_sales_list_count`(...)   select ...