Combining output of two or more select statement

Question

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

Can you show us your queries? And what is your expected result? — Radu Gheorghiu, Aug 13 '13 at 08:00
Are you looking at joining 3 tables together, or merging 3 results set with the same columns? — Adriaan Stander, Aug 13 '13 at 08:01
select t1.ks, t1.[# Tasks], coalesce(t2.[# Late], 0) as [# Late] from (SELECT ks, COUNT() AS '# Tasks' FROM Table GROUP BY ks) t1 left join (SELECT ks, COUNT() AS '# Late' FROM Table WHERE Age > Palt GROUP BY ks) t2 on t1.ks = t2.ks I am using this kind of query it work for on two level of join but I need to use more than two level . please suggest something like this so I can use — user2485642, Aug 13 '13 at 13:12

Giannis Paraskevopoulos · Answer 1 · 2014-08-25T22:05:31.053

22

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

edited Aug 25 '14 at 22:05

answered Aug 13 '13 at 08:04

Giannis Paraskevopoulos

18,261
1
49
69

col t1.qty,t2.qty,t3.qty and row as PUN ,KOL,SOL – user2485642 Aug 13 '13 at 10:41
What is PUN, KOL, SOL ? – Giannis Paraskevopoulos Aug 13 '13 at 10:42
I think those are the aliases he wants for the columns – Radu Gheorghiu Aug 13 '13 at 12:46
select t1.ks, t1.[# Tasks], coalesce(t2.[# Late], 0) as [# Late] from (SELECT ks, COUNT(*) AS '# Tasks' FROM Table GROUP BY ks) t1 left join (SELECT ks, COUNT(*) AS '# Late' FROM Table WHERE Age > Palt GROUP BY ks) t2 on t1.ks = t2.ks I am using this kind of query it work for on two level of join but I need to use more than two level . please suggest something like this so I can use. – user2485642 Aug 13 '13 at 12:56
You should add that on your original post for everyone to see. – Giannis Paraskevopoulos Aug 13 '13 at 13:01
@Giannis Paraskevopoulos: which of these solutions do you consider, in general, best practice? – tumultous_rooster Aug 25 '14 at 21:57
@matt It depends on your case i believe. I guess i would go for option 1 if i wanted the results in one row and option 3 if i wanted the results in separate ones, but it really depends on the specifics of the case. The question here was quite too broad. – Giannis Paraskevopoulos Aug 25 '14 at 22:07
In place of ID I need date is it possible. if yes can u plz help – Vikram Jul 05 '15 at 15:58
@Vikram i think you should explain a bit your case. – Giannis Paraskevopoulos Jul 05 '15 at 16:00
I am having 4 tables(A,B,C,D) with columns are date and amount. My requirement is i need sum of amount from each table group by date. Means resultant table columns should be date, sum(A),sum(B),sum(C),sum(D) – Vikram Jul 05 '15 at 16:11
Then i believe my second sample (joining of the grouped results on date) would be what you want. Just replace id with the date column and qty with the amount column. You would join on the date column. – Giannis Paraskevopoulos Jul 05 '15 at 16:13
Join with ID is OK.. but with date is it accepts or not is my doubt – Vikram Jul 05 '15 at 16:17
Well, I wouldn't like it either, but you need to see what is in common. Since you do not have something else.... Then date it is. Make sure it does not have time portions. – Giannis Paraskevopoulos Jul 05 '15 at 16:21
Only date is the common field. and i will try with that removing time portion. almost 6 hrs this making me to think new each time. Thank you lot sir for your wonderful support – Vikram Jul 05 '15 at 16:30
If you are having trouble removing the time check this answer here http://stackoverflow.com/questions/1177449/best-approach-to-remove-time-part-of-datetime-in-sql-server – Giannis Paraskevopoulos Jul 05 '15 at 16:33
I am using Convert(nvarchar(15),date,103)date is it OK – Vikram Jul 05 '15 at 16:36
I usually go for convert(varchar(8),datefield,112) it gets yyyymmdd format. Then you may cast it to int. – Giannis Paraskevopoulos Jul 05 '15 at 17:12

score 3 · Answer 2 · edited Aug 13 '13 at 08:10

3

Why not create a statement that will fetch them all at once?

SELECT tableA.data1, tableB.data2 FROM tableA, tableB WHERE <condition here>

edited Aug 13 '13 at 08:10

cchana

4,899
3
33
43

answered Aug 13 '13 at 08:04

bry

39
1

score 0 · Answer 3 · answered Jan 27 '17 at 23:43

0

task1 = "select top " & strmcount & " * from nregistration where " & strtotal1
task2 = "select top " & strfcount & " * from nregistration where " & strtotal2

to make execute on parallel

answered Jan 27 '17 at 23:43

user7481218

1

Combining output of two or more select statement

3 Answers3