SQL INSERT INTO SELECT Statement Invalid use of group function

Question

I've the following query:

INSERT INTO StatisticalConsultationAgreement VALUES (
   queryType, entityCode, entityType, queryClass,queryTables,period, 
   COUNT(queryClass),  SUM(numberRecords),  SUM(recordsFound),
   SUM(NorecordsFound), NOW(), 'system');
SELECT
   MONTH(EndDateTimeProcessing),YEAR(EndDateTimeProcessing),
   entityType,
   entityCode,
   queryType,
   queryClass,
   EndDateTimeProcessing as period
FROM agreementFile
WHERE 
  MONTH(EndDateTimeProcessing)=MONTH(DATE_SUB( CURDATE(), INTERVAL 1 MONTH )) 
  AND YEAR(EndDateTimeProcessing)=YEAR(CURDATE())
GROUP BY entityType,entitycode,queryType, queryClass;

When I run the query I get the next mistake:

Error code 1111, SQL state HY000: Invalid use of group function
Line 1, column 1

Executed successfully in 0,002 s.
Line 5, column 2

why ocurre this?

how to fix it?

Answer 1

You are mixing a values statement with a select statement in insert. You only need select. This is my best guess on what you want:

INSERT INTO StatisticalConsultationAgreement 
    SELECT queryType, entityCode, entityType, queryClass,queryTables,period, 
           COUNT(queryClass),  SUM(numberRecords),  SUM(recordsFound),
           SUM(NorecordsFound), NOW(), 'system'
    FROM agreementFile
    WHERE MONTH(EndDateTimeProcessing)=MONTH(DATE_SUB( CURDATE(), INTERVAL 1 MONTH )) AND
          YEAR(EndDateTimeProcessing)=YEAR(CURDATE())
    GROUP BY entityType, entitycode, queryType, queryClass;

However, you should also list the column names for StatisticalConsultationAgreement in the insert statement.