CODEWITHSUNDEEP
sql-server
Manas Ind
Manas Ind

Reputation: 3

How to get the desired output dynamically?

We have two student tables with details in one table and marks in another, we need to calculate the percentage of marks obtained by students and divide them according to percentage and gender. Attached image for further understanding. image

Adding my trial_code:

create table student_details (id int primary key, student_name varchar(20), student_gender varchar(15))

insert into student_details values(1,'test','Male'),(2,'test2','Female')

select * from student_details

create table student_marks(id int primary key, student_id int foreign key references student_details(id) 
                          ,[subject] varchar(20)
                          ,marks int)

insert into student_marks values(1,1,'English',25),(2,2,'English',65)
                               ,(3,1,'Social',25),(4,2,'Social',65)
                               ,(5,1,'Bio',25),(6,2,'Bio',65)
                               ,(7,1,'Maths',25),(8,2,'Maths',65)
                               ,(9,1,'Science',25),(10,2,'Science',65)

select avg(marks) from student_marks
group by student_id

select sum(marks) from student_marks
group by id

create or alter proc student_avg_marks
as
begin

declare @column varchar(50)

select case when avg(sm.marks)<25 then 'Below 25%'
            when (avg(sm.marks)between 25 and 50)  then '25% - 50%'
            when (avg(sm.marks)between 50 and 75)  then '50% - 75%'
            when avg(sm.marks)<25 then 'Above 75%'end as avg_marks,
       count(student_id) as [total no. of students], 
       sum(case when sd.student_gender='Male' then 1 else 0 end),
       sum(case when sd.student_gender='Female' then 1 else 0 end)
from student_details as sd inner join
student_marks as sm on sd.id=sm.student_id
group by sm.student_id

end

exec student_avg_marks

The output should be something like in the image, even if no records are present the avg_marks column should display all records in it.

Fiddle The final output should count no. of students but the result displayed is no. of subjects.

If no of students is 3 and 2 are male and 1 is female then it should represent that and 50 lies in '50 to 75' range.

Upvotes: 0

Views: 101

Answers (2)

Ozan Sen
Ozan Sen

Reputation: 2615

hello please test this:

CREATE OR ALTER PROC student_avg_marks
AS
BEGIN
--declare @column varchar(50)
SELECT V.avg_marks ,isnull(T2.[total no. of students],'') as [No. of Students], isnull(T2.male,'') as Male, isnull(T2.female,'') as Female
FROM (values('Below 25%') ,('25% - 50%') ,('50% - 75%'),('Above 75%'))  V (avg_marks)
LEFT JOIN (
select case when avg(sm.marks) < 25 then 'Below 25%'
        when avg(sm.marks) between 25 and 50  then '25% - 50%'
        when avg(sm.marks) between 50 and 75  then '50% - 75%'
        when avg(sm.marks) > 75 then 'Above 75%'end as avg_marks,
       COUNT(DISTINCT sm.student_id) as [total no. of students], 
       SUM(case when sd.student_gender='Male' then 1 else 0 end) AS male,
       SUM(case when sd.student_gender='Female' then 1 else 0 end) AS female
from student_details as sd inner join
student_marks as sm on sd.id=sm.student_id
group by sd.student_gender) AS T2 ON V.avg_marks = T2.avg_marks
GROUP BY V.avg_marks, T2.[total no. of students], T2.male, T2.female
END

if we execute the query:

GO
EXECUTE student_avg_marks

Result Set: click to see the result_set

Upvotes: 1

Mostafa NZ
Mostafa NZ

Reputation: 382

Use a select with your desired values

create or alter proc student_avg_marks
as
begin

declare @column varchar(50)
SELECT V.avg_marks ,T2.[total no. of students] ,T2.male ,T2.female FROM (values('Below 25%') ,('25% - 50%') ,('50% - 75%'),('Above 75%')) V (avg_marks)
LEFT JOIN (
select case when avg(sm.marks)<25 then 'Below 25%'
            when (avg(sm.marks)between 25 and 50)  then '25% - 50%'
            when (avg(sm.marks)between 50 and 75)  then '50% - 75%'
            when avg(sm.marks)<25 then 'Above 75%'end as avg_marks,
       1 as [total no. of students], 
       case when sd.student_gender='Male' then 1 else 0 end AS male,
       case when sd.student_gender='Female' then 1 else 0 end AS female
from student_details as sd inner join
student_marks as sm on sd.id=sm.student_id
group by sm.student_id ,sd.student_gender) AS T2 ON V.avg_marks = T2.avg_marks

end

Upvotes: 0

Related Questions