CODEWITHSUNDEEP
sqlsql-server
Mukesh Dhiman
Mukesh Dhiman

Reputation: 99

How to accomplish this task using SQL Server

I have a table in this format

So i need a new column studentperformance and the output will be like this 

studentname       studentperformance
   A                   Fail
   B                   Merit
   C                   Pass
   D                   Fail

Upvotes: 3

Views: 103

Answers (4)

Andriy M
Andriy M

Reputation: 77667

Assuming there are always three items per student, here's the idea.

For each studentname:

  1. Count each studentsubjectstatus.

  2. Sort the results first in the descending order of the count, then in the alphabetical order of studentsubjectstatus (so that Fail becomes first in case of ties).

  3. Get the top result.

So, something like this:

SELECT DISTINCT
  studentname,
  studentperformance = (
    SELECT TOP (1)
      s.studentsubjectstatus
    FROM
      dbo.yourtable AS s
    WHERE
      s.studentname = t.studentname
    GROUP BY
      s.studentsubjectstatus
    ORDER BY
      COUNT(*) DESC,
      s.studentsubjectstatus ASC
  )
FROM
  dbo.yourtable AS t
;

If you do not want to rely on the alphabetical order of the values in studentsubjectstatus, you can make sure that Fail comes first by replacing s.studentsubjectstatus ASC with something like this:

CASE s.studentsubjectstatus WHEN 'Fail' THEN 1 ELSE 2 END ASC

Upvotes: 0

Deep
Deep

Reputation: 3202

you can do it using case when aggregate like below :

SELECT studentname,
       CASE
         WHEN Sum(CASE
                    WHEN studentsubjectstatus = 'Merit' THEN 1
                    ELSE 0
                  END) > 1 THEN 'Merit'
         WHEN Sum(CASE
                    WHEN studentsubjectstatus = 'pass' THEN 1
                    ELSE 0
                  END) > 1 THEN 'pass'
         ELSE 'fail'
       END
FROM   yourtable
GROUP  BY studentname

OR

SELECT studentname,
       CASE
         WHEN Sum(CASE
                    WHEN studentsubjectstatus = 'Merit' THEN 1
                    ELSE 0
                  END) > 1 THEN 'Merit'
         ELSE
           CASE
             WHEN Sum(CASE
                        WHEN studentsubjectstatus = 'pass' THEN 1
                        ELSE 0
                      END) > 1 THEN 'pass'
             ELSE 'fail'
           END
       END AS studentperformance
FROM   yourtable 
GROUP  BY studentname

note: > 1 condition can be changed to = 2 if you want to count exact 2 rows with certain studentsubjectstatus.

Upvotes: 1

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20489

Try this:

SELECT
    studentName AS 'Student Name'
    , CASE 
          WHEN Merit >= 2 
              THEN 'Merit'
          WHEN Pass >= 2
              THEN 'Pass'
          ELSE 'Fail'
      END AS 'Student Performance'
(SELECT
    studentName
    , COUNT(CASE WHEN StudentSubjectStatus = 'Merit' THEN 1 ELSE 0 END) AS 'Merit'
    , COUNT(CASE WHEN StudentSubjectStatus = 'Pass' THEN 1 ELSE 0 END) AS 'Pass'

      -- counting how many subjects the student failed is not needed, 
      -- but for example purposes it has been added

    , COUNT(CASE WHEN StudentSubjectStatus = 'Fail' THEN 1 ELSE 0 END) AS 'Fail'
FROM Students
GROUP BY studentName)

Upvotes: 1

Deepshikha
Deepshikha

Reputation: 10264

You can write a query as:

;WITH CTE AS
( SELECT studentname, 
         ROW_NUMBER() OVER (partition BY studentname,studentsubjectstatus ORDER BY educationmarks DESC)
         AS rownum,
         studentsubjectstatus         
  FROM  @student)
 ,CTE2 AS
 (
 SELECT  studentname , MAX(rownum) AS maxrow
 FROM CTE
 GROUP BY studentname
 )
 SELECT DISTINCT T1.studentname
       ,CASE WHEN T1.rownum = 1 THEN 'Fail' ELSE
       T1.studentsubjectstatus END AS studentperformance 
 FROM CTE AS T1
 JOIN CTE2 AS T2 ON T1.studentname=T2.studentname AND T1.rownum=t2.maxrow

Upvotes: 1

Related Questions