Combining output of two or more select statement

sql sql-server-2008 select sqlcommand
user2485642 picture user2485642 · Aug 13, 2013 · Viewed 67k times · Source

How to combine output of two or more SELECT statements, I have multiple tables which are having some data that I need to fetch them so I write multiple SELECT query. Now I want to combine result of the queries so what do I need to do ? I want the output to be: 

t1.qty,t2.qty,t3.qty

Answer

Giannis Paraskevopoulos picture Giannis Paraskevopoulos · Aug 13, 2013

One option would be:

SELECT (SELECT SUM(qty) FROM Table1 WHERE ...),
       (SELECT SUM(qty) FROM Table2 WHERE ...),
       (SELECT SUM(qty) FROM Table3 WHERE ...)

Another would be joining, provided that there is a link:

SELECT * 
FROM   (SELECT ID,SUM(qty) FROM Table1 GROUP BY ID) T1
       JOIN (SELECT ID,SUM(qty) FROM Table2 GROUP BY ID) T2
           ON T1.ID = T2.ID
       JOIN (SELECT ID,SUM(qty) FROM Table3 GROUP BY ID) T3
           ON T1.ID = T3.ID

The above options would be to display results in one row.

You may need union to combine rows:

SELECT qty FROM Table1
UNION
SELECT qty FROM Table2
UNION
SELECT qty FROM Table3

Much more options if you define more specific needs

Related questions

TSQL CASE with if comparison in SELECT statement

I would like to use CASE statement in SELECT. I select from user table, and (as one attribute) I also use nested SQL: SELECT registrationDate, (SELECT COUNT(*) FROM Articles WHERE userId = Users.userId) as articleNumber, hobbies, ... FROM USERS and then …

Read More
Total Row Count in sql query---sql server 2008

My query is as follows BEGIN WITH MyCTE AS ( SELECT T.MusicAlbumTitle ,D.musicTitle ,D.mVideoID ,D.musicFileName ,T.ReleaseDate AS ReleasedDate ,D.MusicLength ,D.musicSinger ,D.MusicVideoID ,D.ExternalLink ,D.CoverImg ,ROW_NUMBER() OVER ( PARTITION BY D.MusicVideoID ORDER …

Read More
Find all tables whose name ends with a certain suffix

I have thousand of tables in database. Some names end with _History. For example : abc_History bcd_History 123_History How do I find all tables which name is end with _History. Some thing like: SELECT table_name FROM sys.tables …

Read More