Returning values from 2 queries to a single row

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

Returning values from 2 queries to a single row

Author

Topic

whitewizzard64
Starting Member

2 Posts

Posted - 2014-09-30 : 10:23:14

Hello, I am hoping to gather some wisdom from you all.

, I have a query set that returns values as part of a data set, I need a new one to return values from two queries to a single row.

select '1' as thekey, 'Total Picks' as Tot,sum(prod_qty) as picks from exceed.aseld, exceed.csymh
where luis_id in ('I','E')
and aseld.whse_id = 1
and (
(aseld.batch_id between goal_beg_batch and goal_end_batch
and monitor_group = 'YK')
or
(aseld.batch_id between goal_beg_batch and goal_end_batch
and monitor_group = 'PA')
or
(aseld.batch_id between goal_beg_batch and goal_end_batch
and monitor_group = 'WN'))
group by '1', 'Total Picks'
Union all
select '2' as thekey, 'Completed Picks' as Com, sum(prod_qty) as picks from exceed.aseld, exceed.csymh
where luis_id in ('I','E')
and aseld.whse_id = 1
and (
(aseld.batch_id between goal_beg_batch and goal_end_batch
and monitor_group = 'YK')
or
(aseld.batch_id between goal_beg_batch and goal_end_batch
and monitor_group = 'PA')
or
(aseld.batch_id between goal_beg_batch and goal_end_batch
and monitor_group = 'WN'))
and wust_id = 'COM'
group by '2', 'Complete Picks'

In short is it possible to get the numbers from keys 1 & 2 on the same row in a new query?

Or if it is easier a query that with give me (completed picks/total picks) = a decimal I can feed to the display as a percentage.

Thank you for any input you may have!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-30 : 10:52:05

Typically you would use a JOIN to return results from two recordsets.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-09-30 : 11:20:28

You should provide consumable test data and alias all your columns so we know what table they come from.

Try something like the following - you may need to change some of the column aliases.


SELECT SUM(C.prod_qty) AS TotalPicks
	,SUM(CASE WHEN C.wust_id = 'COM' THEN C.prod_qty ELSE 0 END) AS CompletedPicks
	,(SUM(CASE WHEN C.wust_id = 'COM' THEN C.prod_qty ELSE 0 END) * 100.0) / SUM(C.prod_qty) AS PercentCompleted
FROM exceed.aseld A
	JOIN exceed.csymh C
		ON A.batch_id BETWEEN C.goal_beg_batch AND C.goal_end_batch
WHERE A.luis_id IN ('I','E')
	AND A.whse_id = 1
	AND C.monitor_group IN ('YK', 'PA', 'WN')

whitewizzard64
Starting Member

2 Posts

Posted - 2014-09-30 : 13:23:33

I have two tables I am pulling data from, exceed.aseld and exceed.csymh,

aseld is a large table but the only columns I need data from are:
prod_qty
luis_id
wust_id
whse_id

csymh is just a table of batch ranges I modify to serve as paramiters for the query.

SYMH_ID
DC_ID
WHSE_ID
MONITOR_GROUP
CSYMH_DESC
CURRENT_BATCH
GOAL_BEG_BATCH
GOAL_END_BATCH
CREATE_USER
CREATE_DTIM
CHANGE_USER
CHANGE_DTIM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources