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

  • you should use `count` (with backticks) as count is a MySQL reserve word or may change the variable name to some other value – Nawaz Sohail Nov 1 '16 at 10:54
  • have changed the variable name..still no change in the error – user109418 Nov 1 '16 at 10:58
  • @NawazSohail COUNT is not reserved (but I agree should not be used as a column or variable name.) – ypercubeᵀᴹ Nov 1 '16 at 11:02
  • Only one INTO clause. – Rick James Nov 2 '16 at 16:35
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 ... ;
  • wow..thats it.Thank you so much for saving my time. – user109418 Nov 1 '16 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

  • I am getting a syntax error while using this query. – user109418 Nov 2 '16 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 '16 at 16:32
  • I may have fixed the syntax error. – Rick James Nov 2 '16 at 16:34
  • Thanks for your valuable comments..the syntax error has gone..but,what does this 'q' stands for?? – user109418 Nov 3 '16 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 '16 at 3:31
0

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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