asked on 4/8/2013

2 selects combined into 1 result set

Hi

needing help with a query, just now i have 2 selects statements doing this...

SELECT sum(TaQty) as 'Pass Qty'
       FROM TBLTRANSACTION WITH (NOLOCK) where tajobkey = @job
       and TaTransCdKey = 'OutAccept'
       and TaStationIdKey = @station
       group by TaQty

       SELECT sum(TaQty) as 'Fail Qty'
       FROM TBLTRANSACTION WITH (NOLOCK) where tajobkey = @job
       and TaTransCdKey = 'OutReject'
       and TaStationIdKey = @station
       group by TaQty

and the output is 2 seperate results e.g.
Pass Qty
1280

Fail Qty
1

but im needing to combine the 2 so that they are returned in same result set (1 row) as
Pass Qty Fail Qty
1280 1

Thanks

Microsoft SQL Server 2008 Microsoft SQL Server

Last Comment

PortletPaul

4/8/2013

PortletPaul

4/8/2013

suggestion 1 of 2

SELECT 'Pass Qty' as Type_of, sum(TaQty) 'Qty'
FROM TBLTRANSACTION WITH (NOLOCK) where tajobkey = @job
and TaTransCdKey = 'OutAccept' 
and TaStationIdKey = @station
group by TaQty

UNION ALL

SELECT 'Fail Qty' as Type_of, sum(TaQty) as 'Qty'
FROM TBLTRANSACTION WITH (NOLOCK) where tajobkey = @job
and TaTransCdKey = 'OutReject'
and TaStationIdKey = @station
group by TaQty

Open in new window

Louis01

4/8/2013

SELECT 'Pass Qty', 'Fail Qty'
  FROM (
        SELECT sum(TaQty) as 'Pass Qty'
        FROM TBLTRANSACTION WITH (NOLOCK) where tajobkey = @job
        and TaTransCdKey = 'OutAccept' 
        and TaStationIdKey = @station
        group by TaQty) t1
	CROSS JOIN (
        SELECT sum(TaQty) as 'Fail Qty'
        FROM TBLTRANSACTION WITH (NOLOCK) where tajobkey = @job
        and TaTransCdKey = 'OutReject'
        and TaStationIdKey = @station
        group by TaQty) t2