3

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

4
  • you should use `count` (with backticks) as count is a MySQL reserve word or may change the variable name to some other value Nov 1, 2016 at 10:54
  • have changed the variable name..still no change in the error
    – user109418
    Nov 1, 2016 at 10:58
  • @NawazSohail COUNT is not reserved (but I agree should not be used as a column or variable name.) Nov 1, 2016 at 11:02
  • Only one INTO clause.
    – Rick James
    Nov 2, 2016 at 16:35

3 Answers 3

4

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 ... ;
1
  • wow..thats it.Thank you so much for saving my time.
    – user109418
    Nov 1, 2016 at 11:01
1

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

9
  • I am getting a syntax error while using this query.
    – user109418
    Nov 2, 2016 at 6:37
  • And the full text of the syntax error is ___? Please note that, when it says "near ...", it is usually pointing exactly at or after the offending token.
    – Rick James
    Nov 2, 2016 at 16:32
  • I may have fixed the syntax error.
    – Rick James
    Nov 2, 2016 at 16:34
  • Thanks for your valuable comments..the syntax error has gone..but,what does this 'q' stands for??
    – user109418
    Nov 3, 2016 at 3:26
  • Hmmm... sounds like a typo. Maybe it is fixed now. Suggest you read the manual on IODKU, I may have goofed in some other way, too.
    – Rick James
    Nov 3, 2016 at 3:31
0

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

2
  • Thankyou for your answer.But the error still persists.Is there anything wrong with that select query??
    – user109418
    Nov 1, 2016 at 10:20
  • I have also declared count as a variable..still showing the error
    – user109418
    Nov 1, 2016 at 10:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.