Create a function called total_methods that returns the count of each unique met

Question

Create a function called total_methods that returns the count of each unique method used on the well. When called, this function should pass well ID as a parameter. Provide the code that would display the well ID and count for each well as returned by the function. Give the results of the function an alias of method_count.

I know it would start with

CREATE FUNCTION total_methods (vwell_id)

BEGIN

DECLARE

employee * employee_id first-name last_name email (0) gender (0) ob A a employee_id a wellid water_sampleA - startdate end-date (o) # sample-id - ph (0) temperature (0) E street (o) E city (o) state (o) s conductivity (o) dissolved_oxygen (o) E zipcode (o) hire-date (0) date time (0) well_id method-id employee_id well # wellid s depth (o) 9 street (o) city (o) - state (o) zipcode (o) method A methodid description -

Explanation / Answer

The total_method function is created below, that returns the count of the method used in well.

Aggregate function COUNT() is used with distinct to get the unique method used in each well. The well id is passed in the function call and function returns the corresponding number of used methods.

Query-

CREATE FUNCTION total_methods(vwell_id INT)
BEGIN
DECLARE method_count INT;
SET method_count = 0
SELECT COUNT(DISTINCT ws.method_id) INTO method_count
FROM water_sample AS ws
INNER JOIN method AS m
ON m.method_id = ws.method_id
WHERE ws.well_id = vwell_id;
RETURN method_count;
END

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.