Adding the results of 2 queries

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Adding the results of 2 queries

Author

Topic

mikeh001
Starting Member

5 Posts

Posted - 2007-06-21 : 21:08:12

Hi,
I have to queries that return tables with the same names. How do i add these 2 so it returns one table?
Thanks for your help.
Mike

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-21 : 21:09:23

Sorry, don't quite understand what you want here. Can you explain more with sample data and expected result ?

KH
[spoiler]Time is always against us[/spoiler]

mikeh001
Starting Member

5 Posts

Posted - 2007-06-21 : 21:19:23

Sorry; thanks for checking!
I run 2 SQL queries on 1 database. Though they look at slightly different data, the resulting tables contain columns with identical names.
How do I create one combined query that returns the result of both tables?
For example:
Table 1 (from query 1) Table 2 (from query 2)
team count team count
red 5 red 6
blue 10 blue 8

Combined table
team count
red 11
blue 8

Hope i made some sense!

mikeh001
Starting Member

5 Posts

Posted - 2007-06-21 : 21:20:27

Sorry, combined blue is 18!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-21 : 21:28:35

[code]SELECT team, SUM(COUNT)
FROM
(
<query 1>

UNION ALL

<query 2>
) d
GROUP BY team[/code]

KH
[spoiler]Time is always against us[/spoiler]

mikeh001
Starting Member

5 Posts

Posted - 2007-06-22 : 12:30:33

Thanks khtan, i was just able to read this; i'll let you know the results in a bit.

mikeh001
Starting Member

5 Posts

Posted - 2007-06-22 : 13:12:17

Oh khtan, i overlooked mentioning this. What if i'm selecting from 2 different fields? I'll copy both queries here and see if they make sense to you.
Query 1
SELECT wrkr.[worker_name] as 'Team',
COUNT(DISTINCT hd1.[workitem_number]) AS 'Open Items',
round(AVG(convert(int,isnull(dbo.GetBusinessMinutes(hd1.[workitem_created_on],getdate()),2))/540.0),2) as 'AVG Age of Incident (Days)'
FROM dbo.HD_workitem_current_view hd1
JOIN dbo.HD_worker_view wrkr
ON hd1.[workitem_assigned_to_worker_id] = wrkr.[worker_id]
WHERE hd1.[workitem_status_lookup_id] IN (300,250,500)
AND wrkr.queue_name is NULL
GROUP BY wrkr.worker_name

Query 2
SELECT wrkr.[queue_name] as 'Team',
COUNT(DISTINCT hd1.[workitem_number]) AS 'Open Items',
round(AVG(convert(int,isnull(dbo.GetBusinessMinutes(hd1.[workitem_created_on],getdate()),2))/540.0),2) as 'AVG Age of Incident (Days)'
FROM dbo.HD_workitem_current_view hd1 JOIN dbo.HD_worker_view wrkr
ON hd1.[workitem_assigned_to_worker_id] = wrkr.[worker_id]
WHERE hd1.[workitem_status_lookup_id] IN (300,250,500)
AND (NOT wrkr.[queue_name] IN ('NULL'))
GROUP BY wrkr.queue_name

The resulting combined query should add the number of 'Open Items' for each team. the teams in these tables have the same names.

Hope this helps.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-23 : 02:29:23

Apply Tan's logic. That will work

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources