Hi i'm trying to create a SQL function but keep getting error code: 1172 Result Consisted of more than one row. Is there anyone who have an idea of what is wrong in the code. I want the function to calculate the avg agelimit and show the games which is over the avg. The results should consist of 18 values.
Delimiter // CREATE FUNCTION fAgeLimit2() returns varchar(1000) BEGIN DECLARE GameName varchar(1000); SELECT DISTINCT game.name into GameName from game, genre, hasgenre WHERE game.name = hasgenre.GameName AND hasgenre.GenreName = genre.Name AND AgeLimit > (SELECT AVG(AgeLimit) FROM genre); return GameName; END //
Answer by Kev Riley ·
You have defined a scalar-valued function, which will return a single value of varchar(1000). But your query is returning more than one row.
What did you intend for this function to do - if you only want a single value back, then you will need to change your query to do just that. If you want multiple rows back, then you should turn the this function into a table-valued function instead.
Created a simple table that adds up points scored per player per game, however I don't know what to do if a player didn't play in one of the games. Null seems to add a 0 value, which renders my calculations incorrect. Help, please.