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.

Answer 2

You are not grouping EndDateTimeProcessing and when you try to do the Insert it can't figure out which EndDateTimeProcessing value, from the grouped rows, it should take. The solution is either you add it on your group clause:

 GROUP BY entityType,entitycode,queryType, queryClass, EndDateTimeProcessing;

Or you use a function group as MAX(), MIN(), etc.

Best Regards

EDIT

As said by Gordon Linoff, you are also mixing the query with the INSERT, everything should be gotten by the query.

The right syntax should be:

INSERT INTO StatisticalConsultationAgreement
SELECT
   'queryType', --I don't know what is the query type so i put it on single quote
   entityCode,
   entityType,
   queryClass,
   queryTables,
   MAX(EndDateTimeProcessing), --Period put on group function MAX, but it cant be grouped below or put into another group function
   COUNT(queryClass),  --
   SUM(numberRecords), --  ASUMING THOSE ARE COLUMNS IN  agreementFile
   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;

The fields MONTH(EndDateTimeProcessing),YEAR(EndDateTimeProcessing), for the query were removed because i didn't know where thouse should be