April 26, 2013 at 2:14 am
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,
IDNAMEHOURS
1AAA0
2BBB10
3CCC0
4DDD0
The Second SQL Statement Result was like below one,
IDNAMEHOURS
1AAA20
2BBB0
3CCC0
4DDD0
After writing the The above UNION Statement i get like below one
IDNAMEHOURS
1AAA0
1AAA20
2BBB0
2BBB10
3CCC0
4DDD0
It was wrong
I want to get result like below one
IDNAMEHOURS
1AAA20
2BBB10
3CCC0
4DDD0
Please give solution to me
Thanks In Advance,
VenkiDesai.
April 26, 2013 at 3:00 am
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
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 26, 2013 at 3:14 am
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
April 27, 2013 at 11:17 pm
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/[/url]
You can also follow my twitter account to get daily updates: @BLantz2455
April 28, 2013 at 8:14 pm
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
April 29, 2013 at 12:59 am
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
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply