CODEWITHSUNDEEP
mysql
Michał Szydłowski
Michał Szydłowski

Reputation: 3409

Why PHPMyAdmin throws an error in my SQL syntax?

It's again PHPMyAdmin... I try to run query on two tables - cards and ownerships (which list all players with the cards they own). And I'd like to create a function which will return (if given the name) the total amount of that card on the market. I defined this in the following way:

CREATE FUNCTION  get_card_overall_count (_name TEXT)
RETURNS INT
DETERMINISTIC
READS SQL DATA

BEGIN
DECLARE res INT;
SET res = SELECT SUM(`ownerships`.`amount`) 
          FROM `ownerships` JOIN `cards` ON `ownerships`.`card_ID` = `cards`.`ID`
          WHERE `cards`.`name` = _name 
          GROUP BY `name`;
RETURN res;
END//

Upvotes: 1

Views: 50

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562260

The SET syntax wants the right hand side to be a scalar, not a query, but you can convince it to run a scalar subquery:

SET res = (SELECT SUM(`ownerships`.`amount`) 
          FROM `ownerships` JOIN `cards` ON `ownerships`.`card_ID` = `cards`.`ID`
          WHERE `cards`.`name` = _name 
          GROUP BY `name`);

Upvotes: 2

Related Questions