asked on

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

PortletPaul

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

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

Open in new window

SOLUTION

Cluskitt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial