Selecting sum() of likes here

Question

In the following table, I'm trying to update each rows parents with the sum of their total votes.

Everything works fine here, except I'd like to do things with a SUM() of all the type = 10 rather than the way I'm doing it now. Right now, what happens is this: if the total of type = 10 change, the total of the parents do not adjust, rather they are added up. I cannot figure out how to add a sum() in there. I came this far. Can you help?

If you check my stored procedure, you'll see how it works.

The below stuff works fine. I'm unsure how to setup a stored procedure on sqlfiddle to demo it.

My Table called likesd with desired results

These results are actually done by the stored procedure below

"id"    "type"  "path"          "likes"
"1"     "1"     "0"             "20"
"2"     "1"     "0"             "20"
"3"     "2"     "1,2"           "20"
"4"     "2"     "1,2,3"         "20"
"5"     "2"     "1,2,3"         "0"
"6"     "3"     "1,2,3,4"       "0"
"7"     "3"     "1,2,3,4"       "0"
"8"     "4"     "1,2,3,4"       "20" 
"9"     "10"    "1,2,3,4,8"     "5" // These type 10 are books
"10"    "10"    "1,2,3,4,8"     "5" // These type 10 are books
"11"    "10"    "1,2,3,4,8"     "5" // These type 10 are books
"12"    "10"    "1,2,3,4,8"     "5" // These type 10 are books

This is stored procedure I'm using

BEGIN
#---- Updater for like totals 21-11-2013

   # Declare variables
   DECLARE procId, procLikes INT(10);
   DECLARE procPath VARCHAR(50);
   DECLARE done INT DEFAULT 0;

   # Declare cursor
   DECLARE cur1 CURSOR FOR SELECT id, path, likes FROM testLikes WHERE type = 10;

   # Declare Handle
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

   # Open Cursor
   OPEN cur1;

   # Begin the Loop
   the_loop: LOOP

    FETCH cur1 INTO procId, procPath, procLikes;

   IF done THEN
    LEAVE the_loop;
   END IF;

   # Do some post processing
    UPDATE testLikes t1 JOIN testLikes t2 ON FIND_IN_SET(t1.id, t2.path)
        SET t1.likes = t1.likes + procLikes // Trying to add a sum(of all type = 10) here.
            // Problem here is, New totals should be sum of all type = 10, but right now, it all adds up.
            WHERE t2.id = procId;

    END LOOP the_loop;

    # Dummy select for MySql's bug
    SELECT id INTO procId FROM testLikes where id = 1;

    CLOSE cur1;

END

One of the principles of database normalization is to not store calculated values. — Dan Bracuk, Nov 22 '13 at 12:51
related to your comment- "how to setup a stored procedure on sqlfiddle to demo it." check this Execute triggers stored procedures on SqlFiddle. Mysql http://stackoverflow.com/questions/12166380/execute-triggers-stored-procedures-on-sqlfiddle-mysql — Damodaran, Nov 22 '13 at 12:51

score 0 · Answer 1 · answered Nov 22 '13 at 12:54

0

I am not fully convinced if that works, but:

 select
type
,SUM(likes) as NUmberofLikes
into temp1
from testLikes
group by type

UPDATE testlikes t1
SET likes= (select NumberOFLikes from temp1 t2 where t1.type=t2.type)

answered Nov 22 '13 at 12:54

Kiril Rusev

745
3
9

Selecting sum() of likes here

1 Answers1