[SOLVED] add to variable in WITH RECURSIVE query MySQL

add to variable in WITH RECURSIVE query MySQL

Basically I want to create a variable, then increase it by the value of the "votes" int for each row my query searches.

Is it possible?

Something like this:

set @tally=0;

WITH RECURSIVE descendants AS
(
SELECT id
FROM ballots
WHERE parent=0
UNION ALL
SELECT t.id
FROM descendants d, ballots t
WHERE t.parent=d.id

@tally+=SELECT voteCount FROM descendants d, ballots t WHERE t.parent=d.id
)
SELECT * FROM descendants;
SELECT @tally;

Solution

Figured it out. I was taking the wrong approach. Rather than adding each individual value, you can just get the sum of everything returned at the end of the search.

SET @tally=0;
WITH RECURSIVE descendants AS (
       SELECT id, votes
       FROM ballots
       WHERE parent=0
    UNION ALL
       SELECT t.id, t.votes
       FROM descendants d, ballots t
       WHERE t.parent=d.id
)
SELECT sum(votes) INTO @tally FROM descendants;
SELECT @tally;