CODEWITHSUNDEEP
sqlsql-server
Ubaid Ashraf
Ubaid Ashraf

Reputation: 875

Combine two different unrelated SQL queries( returning single column results) into one query result with two columns

I have two sql queries. In first I am returing total marks obtained and in other i am returning maximum marks . Now i want to combine both in single query for the purpose of making it into single procedure. Queries are as :

First Query 

   select SUM(MarksObtained) as MarksObtained from tbAnswers where QID IN(Select QID from tbQuestions where ExamID =2)

2nd Query

Select SUM(Marks) as MaxMarks from tbQuestions where ExamID =2

I want something like below to return:

Maxmarks | MarksObtained
------------------------
   100        50

I tried Union, but it returned something like below:

MarksObtained
-------------
100

50

Upvotes: 0

Views: 2169

Answers (3)

vittore
vittore

Reputation: 17579

Even with accepted answer it is make sense to mention that probably the right thing to do is to use separate queries, as they are and just use MARS on the client, in case performance was an issue.

UPDATE: Thats how you can combine several queries and read them all together:

using(var conn = SqlConnection(...)) {
   conn.Open();
   var cmd = conn.CreateCommand()
   cmd.CommandText = 
    @"Select SUM(MarksObtained) as MarksObtained 
      from tbAnswers 
      where QID IN(Select QID from tbQuestions where ExamID =2);"
  + @"Select SUM(Marks) as MaxMarks 
      from tbQuestions 
      where ExamID =2";

   using (var dr = cmd.ExecuteReader) {
        ... // read MarksObtained
        dr.NextResult()
        ... // readMaxMarks
        dr.Close()
   }
   conn.Close()
}

MSDN IDataReader.NextResult

Upvotes: 1

Mark Brackett
Mark Brackett

Reputation: 85645

Well, since the queries are unrelated, you can just throw them into the select:

SELECT
   (
      select SUM(MarksObtained)  
      from tbAnswers where QID IN (
          Select QID from tbQuestions where ExamID = 2
      )
   ) as MarksObtained,
   (
       Select SUM(Marks)   
       from tbQuestions where ExamID = 2
   ) as MaxMarks

Upvotes: 3

Anup Shah
Anup Shah

Reputation: 1254

    ;WITH    CTE1 ( MarksObtained )
              AS ( SELECT   SUM(MarksObtained) AS MarksObtained
                   FROM     tbAnswers
                   WHERE    QID IN ( SELECT QID
                                     FROM   tbQuestions
                                     WHERE  ExamID = 2 )
                 ),
            CTE2 ( MaxMarks )
              AS ( SELECT   SUM(Marks) AS MaxMarks
                   FROM     tbQuestions
                   WHERE    ExamID = 2
                 )
        SELECT  MaxMarks,MarksObtained
        FROM    CTE1 ,
                CTE2

Upvotes: 0

Related Questions