Query to combine multiple Select statements into single result set in SQL Server

smile

SOLVED CLOSED ACTIVE ANSWERED
User: smile
Posted: on Apr 30, 2019 12:04 AM
Forum: SQL Server
Answer: 1
Views: 1526

how two tables can be combined in a single tabular format?

I have two sql statements

statement 1:

                        SELECT top 1 * FROM (
                            SELECT  AdmissionNo,sum(Max) 'Total',sum(Mark) 'Obtain'
                            ,dense_rank() over(partition BY SUM(Max) order by SUM(Mark) DESC) AS RANK
                            ,Round(sum(Mark) * 100/ sum(Max),1) as Percentage
                            ,(CASE WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 90 THEN 'A+'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 80 THEN 'A'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 70 THEN 'B'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 60 THEN 'C'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 50 THEN 'D'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 40 THEN 'E'
                            ELSE 'FAIL' END ) AS Grade
                            ,(CASE WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 90 THEN 'OutStanding'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 80 THEN 'Excellent'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 70 THEN 'Very Good'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 60 THEN 'Good'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 50 THEN 'Satisfactory'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 40 THEN 'Work Hard'
                            ELSE 'FAIL' END ) AS Remarks
                            ,(CASE WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 90 THEN 'OutStanding'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 80 THEN 'His/Her performance has been outstanding.Keep it up.'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 70 THEN 'Keep up the good work.All the best.'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 60 THEN 'He/She has the potential to do more.All the best.'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 50 THEN 'He/She has been doing good in studies.'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 40 THEN 'He/She needs to work hard in order to improve grades.'
                            ELSE 'He/She is advised to pay attention towards studies.' END ) AS TRemarks
                            FROM tblSetMarks  
                            GROUP BY AdmissionNo) t 
                        order by Rank asc;

statement 2

                        SELECT top 1 * FROM (
                            SELECT  AdmissionNo,sum(Max) 'Total',sum(Mark) 'Obtain'
                            ,dense_rank() over(partition BY SUM(Max) order by SUM(Mark) DESC) AS RANK
                            ,Round(sum(Mark) * 100/ sum(Max),1) as Percentage
                            ,(CASE WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 90 THEN 'A+'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 80 THEN 'A'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 70 THEN 'B'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 60 THEN 'C'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 50 THEN 'D'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 40 THEN 'E'
                            ELSE 'FAIL' END ) AS Grade
                            ,(CASE WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 90 THEN 'OutStanding'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 80 THEN 'Excellent'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 70 THEN 'Very Good'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 60 THEN 'Good'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 50 THEN 'Satisfactory'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 40 THEN 'Work Hard'
                            ELSE 'FAIL' END ) AS Remarks
                            ,(CASE WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 90 THEN 'OutStanding'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 80 THEN 'His/Her performance has been outstanding.Keep it up.'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 70 THEN 'Keep up the good work.All the best.'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 60 THEN 'He/She has the potential to do more.All the best.'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 50 THEN 'He/She has been doing good in studies.'
                            WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 40 THEN 'He/She needs to work hard in order to improve grades.'
                            ELSE 'He/She is advised to pay attention towards studies.' END ) AS TRemarks
                            FROM tblSetMarks  
                            GROUP BY AdmissionNo) t 
                        order by Rank desc;

Download FREE API for Word, Excel and PDF in ASP.Net

pandeyis...

0 5

Reply
ANSWER
User: pandeyism
Replied: on Apr 30, 2019 02:18 AM
Report

Hi smile,

Refer below query.

SQL

DECLARE @tblSetMarks AS TABLE(AdmissionNo VARCHAR(10), Max INT, Mark INT, Percengage INT, Grade VARCHAR(10))
INSERT INTO @tblSetMarks VALUES('R-01',75,60,80,'A')
INSERT INTO @tblSetMarks VALUES('R-02',75,45,60,'B')
INSERT INTO @tblSetMarks VALUES('R-03',75,30,40,'C')
INSERT INTO @tblSetMarks VALUES('R-04',75,24,32,'D')

SELECT * FROM (SELECT top 1 * FROM (
            	 SELECT  AdmissionNo,sum(Max) 'Total',sum(Mark) 'Obtain'
            	 ,dense_rank() over(partition BY SUM(Max) order by SUM(Mark) DESC) AS RANK
            	 ,Round(sum(Mark) * 100/ sum(Max),1) as Percentage
            	 ,(CASE WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 90 THEN 'A+'
            	 WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 80 THEN 'A'
            	 WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 70 THEN 'B'
            	 WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 60 THEN 'C'
            	 WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 50 THEN 'D'
            	 WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 40 THEN 'E'
            	 ELSE 'FAIL' END ) AS Grade
            	 ,(CASE WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 90 THEN 'OutStanding'
            	 WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 80 THEN 'Excellent'
            	 WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 70 THEN 'Very Good'
            	 WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 60 THEN 'Good'
            	 WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 50 THEN 'Satisfactory'
            	 WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 40 THEN 'Work Hard'
            	 ELSE 'FAIL' END ) AS Remarks
            	 ,(CASE WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 90 THEN 'OutStanding'
            	 WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 80 THEN 'His/Her performance has been outstanding.Keep it up.'
            	 WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 70 THEN 'Keep up the good work.All the best.'
            	 WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 60 THEN 'He/She has the potential to do more.All the best.'
            	 WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 50 THEN 'He/She has been doing good in studies.'
            	 WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 40 THEN 'He/She needs to work hard in order to improve grades.'
            	 ELSE 'He/She is advised to pay attention towards studies.' END ) AS TRemarks
            	 FROM @tblSetMarks 
            	 GROUP BY AdmissionNo
) t
ORDER BY Rank ASC) s
UNION ALL
SELECT * FROM (SELECT top 1 * FROM (
                SELECT  AdmissionNo,sum(Max) 'Total',sum(Mark) 'Obtain'
                ,dense_rank() over(partition BY SUM(Max) order by SUM(Mark) DESC) AS RANK
                ,Round(sum(Mark) * 100/ sum(Max),1) as Percentage
                ,(CASE WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 90 THEN 'A+'
                WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 80 THEN 'A'
                WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 70 THEN 'B'
                WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 60 THEN 'C'
                WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 50 THEN 'D'
                WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 40 THEN 'E'
                ELSE 'FAIL' END ) AS Grade
                ,(CASE WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 90 THEN 'OutStanding'
                WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 80 THEN 'Excellent'
                WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 70 THEN 'Very Good'
                WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 60 THEN 'Good'
                WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 50 THEN 'Satisfactory'
                WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 40 THEN 'Work Hard'
                ELSE 'FAIL' END ) AS Remarks
                ,(CASE WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 90 THEN 'OutStanding'
                WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 80 THEN 'His/Her performance has been outstanding.Keep it up.'
                WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 70 THEN 'Keep up the good work.All the best.'
                WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 60 THEN 'He/She has the potential to do more.All the best.'
                WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 50 THEN 'He/She has been doing good in studies.'
                WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 40 THEN 'He/She needs to work hard in order to improve grades.'
                ELSE 'He/She is advised to pay attention towards studies.' END ) AS TRemarks
                FROM @tblSetMarks 
                GROUP BY AdmissionNo
) t
ORDER BY Rank DESC) s

Output

AdmissionNo	Total	Obtain	RANK	Percentage	Grade	Remarks	TRemarks
R-01	75	60	1	80	A	Excellent	His/Her performance has been outstanding.Keep it up.
R-04	75	24	4	32	FAIL	FAIL	He/She is advised to pay attention towards studies.

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Query to combine multiple Select statements into single result set in SQL Server

Company

Explore

Follow us

Contact Us