CODEWITHSUNDEEP
sqlsql-server-2008
Fox
Fox

Reputation: 59

Combine two sql server queries in one

How to combine these 2 selects in one 

 SELECT TOP 5 Sum([NumOfChats]) AS [NumOfChats]
            ,[CaseTypeName]
            ,[CaseSubTypeName]
      FROM #Result2
      GROUP BY [CaseTypeName]
            ,[CaseSubTypeName]

      SELECT Sum([NumOfChats]) AS [TotalNumOfChats]           
      FROM #Result2

First query sumerize top 5 NumOfChats and the second one all NumOfChats.

So i want to get in the same row for example: 5, Some_CaseTypeName, Some_CaseSubTypeName, 10(this is the total num of chats)

Upvotes: 1

Views: 60

Answers (2)

Fox
Fox

Reputation: 59

DECLARE @T int = (SELECT Sum([NumOfChats])   
      FROM #Result2 )

  SELECT TOP 5 Sum([NumOfChats]) AS [NumOfChats]
        ,[CaseTypeName]
        ,[CaseSubTypeName]
        ,@T [TotalNumOfChats]    
  FROM #Result2
  GROUP BY [CaseTypeName]
        ,[CaseSubTypeName]

Upvotes: 0

jarlh
jarlh

Reputation: 44766

Use UNION ALL, select NULL in second query to get same number of columns as in first query:

SELECT TOP 5 Sum([NumOfChats]) AS [NumOfChats]
            ,[CaseTypeName]
            ,[CaseSubTypeName]
      FROM #Result2
      GROUP BY [CaseTypeName]
            ,[CaseSubTypeName]
UNION ALL
      SELECT Sum([NumOfChats]) AS [TotalNumOfChats], NULL, NULL           
      FROM #Result2

Upvotes: 2

Related Questions