Practices of Aggregate function
-------------------------------
| S_id | c_id | GPA |
| 1 | 1 | 3.5 |
| 1 | 2 | 3.7 |
| 1 | 3 | 4.0 |
| 1 | 4 | 3.8 |
| 2 | 1 | 4.0 |
| 3 | 2 | 3.0 |
create table res (
s_id int,
c_id int,
gpa decimal(4,2)
);
insert into res values
(1,1,3.5),
(1,2,3.7),
(1,3,4.0),
(1,4,3.8),
(2,1,4.0),
(2,2,3.0);
select s_id, avg(gpa) as CPGA, count(*) as n_course
from res
group by s_id;
Problem: Calculate average GPA (CGPA) for student : 1
select s_id, avg(gpa) as CPGA, count(*) as n_course
from res
where s_id=1;
This following SQL will provide CGPA but for student id =1;
select avg(gpa)
from res
where s_id=1;
To get CGPA of other students it needs to change the query. Now to solve this problem we can write a procedure / function where you need to supply student id and function will give you CGPA. i.e. input : s_id , output: CGPA
Step 1:Select Database
Step 2:Select Routine
Step 3: Parameter box: INT: st_id int
Step 4: Parameter box: OUT: cgpa decimal(4,2)
Step 5: In the main box:
select avg(gpa) into cgpa
from res
where s_id=st_id;
or you can run the following SQL directly from SQL menue:
CREATE PROCEDURE cgpa(
IN st_id INT,
OUT cgpa DECIMAL(4,2)
)
select avg(gpa) into cgpa
from res
where s_id=st_id;
Calling the Function:
----------------------
call cgpa(1,@cgpa);
select @cgpa;
Multiple Output
To get CGPA and no. of cources of other students it needs to change the query. Now to solve this problem we can write a procedure / function where you need to supply student id and function will give you CGPA. i.e. input : s_id , output: CGPA, no_cources
CREATE PROCEDURE cgpa_cources(
IN st_id INT,
OUT cgpa DECIMAL(4,2),
OUT no_cources int
)
select avg(gpa),count(*) into cgpa,no_cources
from res
where s_id=st_id;
call cgpa_cources(1,@cgpa,@no_cources);
select @cgpa, @no_cources;
Statlearner
Statlearner