CODEWITHSUNDEEP
sql-servert-sql
Fri3nds M
Fri3nds M

Reputation: 3

How can I show these 3 query result in one table in SSMS?

WHILE @i <= @stsem
BEGIN
    SELECT
        SUM(sb.totalmarks) AS Sub_total,
        SUM(sb.credit) AS Credit_total, 
        SUM(a.totalmarks) AS Total_obt, 
        SUM(a.rankpoint) AS RP_total,
        (SUM(rankpoint) / SUM(sb.credit)) AS SGPA,
        @i AS Sem  
    FROM
        umms.dbo.[dt_result] AS a 
    JOIN
        umms.dbo.[dt_subjectdetails] AS sb ON a.paperid = sb.paperid  
    WHERE
        studentid = 17780 
        AND btid = 495 
        AND a.semid = @i  
        AND (grade != 'F' OR grade != 'Ab' OR grade != 'IN')
              
    IF @i = 10
        BREAK; 

    SET @i = @i + 1;
END;

SELECT SUM(b.credit) as TotalCredit,SUM(rankpoint) as TotalGradepoint ,(SUM(rankpoint)/SUM(b.credit)) as CGPA FROM umms.dbo.[dt_result] as a join umms.dbo.[dt_subjectdetails] as b on a.paperid=b.paperid where studentid=17780 and btid=495

This query executes 3 times.

enter image description here

Upvotes: 0

Views: 98

Answers (2)

Charlieface
Charlieface

Reputation: 71374

It seems a simple filter on a.semid and a GROUP BY on the same column should do the trick:

SELECT
    SUM(sb.totalmarks) AS Sub_total,
    SUM(sb.credit) AS Credit_total, 
    SUM(a.totalmarks) AS Total_obt, 
    SUM(a.rankpoint) AS RP_total,
    (SUM(rankpoint) / SUM(sb.credit)) AS SGPA,
    a.semid AS Sem  
FROM
    umms.dbo.[dt_result] AS a 
JOIN
    umms.dbo.[dt_subjectdetails] AS sb ON a.paperid = sb.paperid  
WHERE
    studentid = 17780 
    AND btid = 495 
    AND a.semid >= 1 AND a.semid <= 10  
    AND (grade != 'F' OR grade != 'Ab' OR grade != 'IN')
GROUP BY
    a.semid;

The second query could potentially be combined also, by using a ROLLUP, but the grade and semid filters make this more difficult.

The logic grade != 'F' OR grade != 'Ab' OR grade != 'IN' appears incorrect. I suggest you take a look at it.

Upvotes: 1

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

You don't seem to need a loop here, try this instead

;WITH CTE
AS
(
    SELECT 
        RN = 1
    UNION ALL
    SELECT
        RN = RN +1
        FROM CTE
            WHERE RN <10
)
SELECT
    SUM(sb.totalmarks) AS Sub_total,
    SUM(sb.credit) AS Credit_total, 
    SUM(a.totalmarks) AS Total_obt, 
    SUM(a.rankpoint) AS RP_total,
    (SUM(rankpoint) / SUM(sb.credit)) AS SGPA,
    CTE.RN AS Sem  
    FROM
        umms.dbo.[dt_result] AS a 
    JOIN
        umms.dbo.[dt_subjectdetails] AS sb ON a.paperid = sb.paperid  
    JOIN CTE 
        ON a.semid = CTE.RN
    WHERE
        studentid = 17780 
        AND btid = 495  
        AND (grade != 'F' OR grade != 'Ab' OR grade != 'IN')
    GROUP BY 
        CTE.RN

Upvotes: 0

Related Questions