CODEWITHSUNDEEP
sqlsql-server
user10324075
user10324075

Reputation:

how to combine the result of one column to another

I am trying to create a student database but i am stuck up the given requirement.

I was given to create 2 tables one with students register number , subjects ans total and another table with student_name , total, rank. i can understand that here we need to use the student name separately and combine it later but the part that got me struck in the student subjects may vary means if student no :1 is having 3 subjects means student no : 2 may have 2 and student no: 3 may have 5 and based on this we have to put ranking order

CREATE TABLE Student
(StudentID int, StudentName varchar(6), Details varchar(1));    


INSERT INTO Student
(StudentID, StudentName, Details)
  VALUES
(1, 'John', 'X'),
(2, 'Paul', 'X'),
(3, 'George', 'X'),
(4, 'Paul', 'X');

CREATE TABLE Subject
(SubjectID varchar(1), SubjectName varchar(7));    

INSERT INTO Subject
  (SubjectID, SubjectName)
VALUES
('M', 'Math'),
('E', 'English'),
('H', 'History');

CREATE TABLE Mark
(StudentID int, SubjectID varchar(1), MarkRate int);    

INSERT INTO Mark
 (StudentID, SubjectID, MarkRate)
VALUES
(1, 'M', 90),
(1, 'E', 100),
(2, 'M', 95),
(2, 'E', 70),
(3, 'E', 95),
(3, 'H', 98),
(4, 'H', 90),
(4, 'E', 100);

I need 2 outputs 1st one is 

ID  |subjects |marks
----------------------------------------------------------
1     maths     98
1     science   87
1     social    88
2     maths     87
2     english   99
3     maths     96
3     evs       100
3     social     88
3     history    90

and the second table as

NO  |name |total|rank
----------------------------------------------------------
1   xxx     123     1
2   yyy     456     2
3   zzz     789      3

I need output like this for n number of entries

Upvotes: 2

Views: 152

Answers (5)

GMB
GMB

Reputation: 222672

I understand that first query lists the marks of each student

SELECT
    m.StudentID as ID,
    s.SubjectName as subjects,
    m.MarkRate as marks
FROM 
    Mark m
    INNER JOIN Subject s on m.SubjectID = m.SubjectID 
ORDER BY 
    m.StudentID,
    s.SubjectName

Second query gives the total mark of Students with their rank.

SELECT 
    X.StudentID,
    X.StudentName,
    ROWNUMBER() OVER ( ORDER BY X.TotalMark desc) as Rank
FROM (
    SELECT 
        m.StudentID,
        s.StudentName,
        sum(m.MarkRate) TotalMark
    FROM 
        Mark m
        INNER JOIN Student s on s.StudentID = m.StudentID 
    GROUP BY 
        m.StudentID,
        s.StudentName
) X
ORDER BY X.TotalMark desc

Upvotes: 1

Suraj Kumar
Suraj Kumar

Reputation: 5653

You can try the following query for both output.

SELECT
    Student.StudentID as ID,
    [Subject].SubjectName as subjects,
    Mark.MarkRate as marks
FROM 
    Student
    INNER JOIN Mark on Student.StudentID = Mark.StudentID
    INNER JOIN [Subject] on [Subject].SubjectID = Mark.SubjectID 
ORDER BY 
    Student.StudentID,
    SubjectName

Below query for second output. Here as you have said for those who has less mark got Rank 1 otherwise you can put Order By Total Desc in below query for higher marks rank 1. 

SELECT ID, StudentName, Total, Row_number() Over (Order BY Total) Ranks FROM(
SELECT Id, StudentName, SUM(marks) as Total FROM (
SELECT
    Student.StudentID as ID,
    [Subject].SubjectName as subjects,
    Mark.MarkRate as marks,
    Student.StudentName
FROM 
    Student
    INNER JOIN Mark on Student.StudentID = Mark.StudentID
    INNER JOIN [Subject] on [Subject].SubjectID = Mark.SubjectID 
)Tot
Group By Id, StudentName
)Ranks

Upvotes: 0

Fariraishe Shumba
Fariraishe Shumba

Reputation: 45

1 st one
SELECT 
A.StudentID AS [ID],
C.SubjectName AS [subjects],
B.MarkRate AS [marks] 
FROM STUDENT A JOIN Mark B ON A.StudentID=B.StudentID JOIN Subject C ON C.SubjectID=B.SubjectID

2nd one
SELECT 
A.StudentID AS [NO],
A.StudentName AS [name],
SUM(B.MarkRate) AS [total],
ROW_NUMBER() OVER(ORDER BY SUM(B.MarkRate) DESC) AS [rank]
FROM STUDENT A JOIN Mark B ON A.StudentID=B.StudentID JOIN Subject C ON C.SubjectID=B.SubjectID
GROUP BY A.StudentID,A.StudentName 
ORDER BY [total] DESC

Upvotes: 2

Chanukya
Chanukya

Reputation: 5893

select StudentID ,SubjectName ,MarkRate from
 #Mark a join #Subject b on a.SubjectID=b.SubjectID

output

StudentID   SubjectName MarkRate
1              Math       90
1              English    100
2              Math       95
2              English    70
3              English    95
3              History    98
4              History    90
4              English    100

Second Query

  with cte as
    (
    select a.StudentID,StudentName,sum(MarkRate)MarkRate  from #Student a join #Mark B on a.StudentID=b.StudentID
    group by a.StudentID,StudentName
    )
    select *,rank() over( order by MarkRate desc) as rn  from cte

output

StudentID   StudentName MarkRate    rn
3             George    193         1
4             Paul      190         2
1             John      190         2
2             Paul      165         4

Upvotes: 1

Zeki Gumus
Zeki Gumus

Reputation: 1484

To get the Mark list with subject name is simply this : you need to use JOIN :

SELECT M.StudentId
        ,SU.SubjectName
        ,M.MarkRate
FROM Mark M
INNER JOIN  Subject SU ON M.SubjectID = SU.SubjectID

To get total marks with ranks you need to use GROUP BY and RANK() function :

SELECT M.StudentId
        ,ST.StudentName
        ,SUM(MarkRate) Total
        ,RANK() OVER(ORDER BY SUM(MarkRate) ) Rank
        --,RANK() OVER(ORDER BY SUM(MarkRate) DESC) Rank
FROM Mark M
INNER JOIN  Student ST ON M.StudentId = ST.StudentId
GROUP BY M.StudentId
        ,ST.StudentName

Upvotes: 1

Related Questions