Clever Geek Handbook

How to accomplish this task using SQL Server

I have a table in this format

  • If a student has two studentsubjectstatus

    , a Merit

    studentperformance

    - Merit

    .
  • If a student has two studentsubjectstatus

    , and Pass

    studentperformance

    -Pass

  • Else is Fail

So, I need a new studentperformance command and the result will be like this:

studentname       studentperformance
   A                   Fail
   B                   Merit
   C                   Pass
   D                   Fail
+3


source to share


4 answers


You can write your query like:



;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
+1


source


Try the following:



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)
+1


source


you can do it using the aggregate case 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: the > 1

condition can be changed to = 2

if you want to count the exact 2 lines with a specific one studentsubjectstatus

.

+1


source


Assuming there are always three subjects per student, here's an idea.

For everyone studentname

:

  • Count each one studentsubjectstatus

    .

  • Sort the results first in descending order, and then alphabetically studentsubjectstatus

    (so that Fail

    becomes first in the case of links).

  • 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 don't 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
0


source






More articles:


All Articles