Error 1064 - You have an error in your SQL syntax;

Question

I am new to developing field, and I have encountered an error while creating a trigger.Could anyone please figure out what is the error?

DELIMITER $$

CREATE TRIGGER update_feedback_dashboard
AFTER INSERT
ON tblcustomfeedback FOR EACH ROW

BEGIN

DECLARE averageScore float default 0;
DECLARE avgscore float default 0;
DECLARE IdCount float default 0;

SELECT SUM(avg_score) into avgscore, COUNT(Id) into count FROM tblcustomfeedback  WHERE feedbackDate = NOW() ORDER BY Id DESC LIMIT 1;

SET averageScore  =avgscore;
SET IdCount  =  count;

IF (SELECT count(*) FROM tblfeedbackdashbd WHERE Date=CURDATE()) = 0 
THEN
 INSERT INTO tblfeedbackdashbd(AverageScoresAday ,Date)     VALUES(averageScore/IdCount,CURDATE());
ELSE   
 UPDATE tblfeedbackdashbd SET AverageScoresAday=averageScore/IdCount  WHERE   Date=CURDATE();
END IF;
END $$

The error encountered is,

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COUNT(Id) into count FROM tblcustomfeedback WHERE feedbackDate = NOW() ORDER BY' at line 11

Answer 1

The syntax is not:

SELECT id INTO @x, data INTO @y ...

but it is:

SELECT id, data INTO @x, @y ...

Use:

SELECT SUM(avg_score), COUNT(Id)
  INTO averageScore, IdCount
FROM ... ;

Answer 2

You are working much too hard. A single statement does all the work:

INSERT INTO  tblfeedbackdashbd (AverageScoresAday, Date)
    SELECT  SUM(avg_score) / COUNT(*),
            CURDATE()
        FROM  tblcustomfeedback
        WHERE  feedbackDate = NOW()   -- ??
        ORDER BY  Id DESC
        LIMIT  1
    ON DUPLICATE KEY UPDATE
        AverageScoresAday = (SUM(avg_score) + AverageScoresAday) / COUNT(*);

I assume you have UNIQUE(Date)

COUNT(Id) should probably be COUNT(*).

This is probably not what you wanted: WHERE feedbackDate = NOW()

Reference; Reference

Answer 3

You have not declared count variable first, add DECLARE count INT 0; right after BEGIN