SQL own Function with sum and avg-CodePudding

I tried to code A own function that adds the values (NumberStars) together and then calculates an average value from th NUmberStars.

Numberstar is a value that gets some numbers for example it gets sometimes the value 5 sometimes 4 or also 2. And the function should add/sum all these values and then calculate the average of them the value is taken from the table:

 CREATE TABLE Rating (
     client_ID NUMBER NOT NULL,
     Tutor_ID NUMBER NOT NULL,
     NumberStars NUMBER NOT NULL,
     Comment VARCHAR2(100) NULL);

CREATE OR REPLACE FUNCTION avgRating( NumberStars NUMBER)
RETURN number
IS
avgRat NUMBER;
BEGIN 
select sum (NumberStars) INTO avgRat FROM Rating;
select avg (avgRat) ;
Return avgRat;
END;

This is what i tried, but sorry that is my first own function

CodePudding user response：

You do not need to write a function for this as there are built-in SUM and AVG functions.

If you want to sum the stars for each tutor then you can use:

SELECT tutor_id,
       SUM(NumberStars) AS total_stars
FROM   rating
GROUP BY tutor_id

If you then wanted to calculate the average of the total stars for all tutors, you can wrap that in an outer query and take the average:

SELECT AVG(total_stars) AS avg_total_stars
FROM   (
  SELECT tutor_id,
         SUM(NumberStars) AS total_stars
  FROM   rating
  GROUP BY tutor_id
)

Or, if you wanted to return every tutor and include the overall average as well you can use the analytic AVG function:

SELECT tutor_id,
       SUM(NumberStars) AS total_stars,
       AVG(SUM(NumberStars)) OVER () AS avg_total_stars
FROM   rating
GROUP BY tutor_id

In this case, the GROUP BY and aggregation will be calculated first and then the analytic function will be applied OVER the entire result set (since no smaller partitions are specified).

CodePudding user response：

I suppose, you want to get the average rating of a specific tutor? Then you could just go with:

CREATE OR REPLACE FUNCTION avgRating (Tutor NUMBER)
RETURN number
IS
avgRat NUMBER;
BEGIN 
SELECT AVG(NumberStars) AS avgRat FROM Rating WHERE Tutor_ID = Tutor;
Return avgRat;
END;

The AVG function will automatically calculate the average of the given field, regarding the WHERE condition.

(Passing in the NumberStars into the function does not make sense from my point of view - otherwise, please clarify your question.)