Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to merge two sql select statement results I tried it but it not get please see it in detailed explination with my query Expand / Collapse
Author
Message
Posted Friday, April 26, 2013 2:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 3:02 AM
Points: 25, Visits: 72
Hi All,

My Query Goes Like This,

Select TT.ID,TT.NAME,ROUND((CAST((Sum(TJA.MINS)) as FLOAT)/60),0) AS HOURS
From MYTABLE1 as TT INNER JOIN MYTABLE2 as TB
On TT.ID=TB.ID INNER JOIN MYTABLE3 as TH
On TB.BID=TH.BID INNER JOIN MYTABLE4 as TJA
On TJA.HID=TH.HID
Where TJA.JID=41
group by TT.ID,TT.NAME
UNION
Select TJA.ID,TT.NAME,ROUND((CAST((Sum(TJA.MINS)) as FLOAT)/60),0) AS HOURS
From MYTABLE1 as TT INNER JOIN MYTABLE2 as TJA
On TT.ID=TJA.ID
wHERE TJA.JID=41
group by TJA.ID,TT.NAME

The First SQL Statement Result was like below one,

ID NAME HOURS
1 AAA 0
2 BBB 10
3 CCC 0
4 DDD 0

The Second SQL Statement Result was like below one,

ID NAME HOURS
1 AAA 20
2 BBB 0
3 CCC 0
4 DDD 0

After writing the The above UNION Statement i get like below one

ID NAME HOURS
1 AAA 0
1 AAA 20
2 BBB 0
2 BBB 10
3 CCC 0
4 DDD 0

It was wrong
I want to get result like below one

ID NAME HOURS
1 AAA 20
2 BBB 10
3 CCC 0
4 DDD 0

Please give solution to me

Thanks In Advance,
VenkiDesai.
Post #1446842
Posted Friday, April 26, 2013 3:00 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 9, 2014 7:34 AM
Points: 2,666, Visits: 4,736
What would be the expected output if your results looked like this and why?

The Second SQL Statement Result was like below one,

ID NAME HOURS
1 AAA 20
2 BBB 30
3 CCC 0
4 DDD 0

After writing the The above UNION Statement i get like below one

ID NAME HOURS
1 AAA 10
2 BBB 10
3 CCC 0
4 DDD 0



Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1446856
Posted Friday, April 26, 2013 3:14 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 1:17 AM
Points: 1,188, Visits: 1,552
I'm short on time right now, but if you adapt this to your tables, it should get you what you need

select Table_1.ID, Table_1.name, (sum(Table_1.hours) + sum(Table_2.hours)) as hours
from Table_1 inner join Table_2
on Table_1. id = Table_2.id
and Table_1.name = Table_2.name
group by Table_1.ID, Table_1.name

would give

1 AAA 20
2 BBB 10
3 CCC 0
4 DDD 0

Bex
Post #1446866
Posted Saturday, April 27, 2013 11:17 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 8, 2013 7:16 PM
Points: 221, Visits: 132
Some more sample data from your tables would be extremely helpful, not just the results.

--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/
You can also follow my twitter account to get daily updates: @BLantz2455
Post #1447310
Posted Sunday, April 28, 2013 8:14 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, October 19, 2014 3:52 PM
Points: 364, Visits: 385
This is because the UNION keyword combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union.

Due to the brevity of the sample data and results, I assume that you are wanting the total hours across both queries for each ID. If that is the case you will need to write an aggregate with GROUP BY, something like this:

select t.ID, t.NAME, sum(t.HOURS)
from (
select ID, NAME, HOURS
from first_query_results
union all
select ID, NAME, HOURS
from second_query_results
) t
group by t.ID, t.NAME

Post #1447355
Posted Monday, April 29, 2013 12:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:54 AM
Points: 6,813, Visits: 14,028
UNION on it own dedupes the result set which is almost certainly not what you want.
Try combining the two queries into one so you're not reading tables twice, unnecessarily:

SELECT 
TT.ID, TT.NAME,
ROUND((CAST((Sum(TJA.MINS)) as FLOAT)/60),0) AS HOURS,
MAX(x.[HOURS]) AS Hours2
FROM MYTABLE1 as TT
INNER JOIN MYTABLE2 as TB
On TT.ID=TB.ID
INNER JOIN MYTABLE3 as TH
On TB.BID=TH.BID
INNER JOIN MYTABLE4 as TJA
On TJA.HID=TH.HID
CROSS APPLY (
SELECT ROUND((CAST((SUM(TJA.MINS)) AS FLOAT)/60),0) AS [HOURS]
FROM MYTABLE2_FromSecondQuery q2
WHERE q2.ID = TT.ID
) x
WHERE TJA.JID=41
GROUP BY TT.ID,TT.NAME



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1447380
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse