Converting two Select Querys into One Query

Question

Dear Experts Can you please suggest How do i Convert the following two queries into one query (SELECT SUM(Qty+QtyFree) AS TOT_IN FROM detail AS D WHERE TTYPE IN ('P','R','T') GROUP BY BID), (SELECT SUM(Qty+QtyFree) AS TOT_OUT FROM detail WHERE TTYPE IN ('S','B','A') GROUP BY BID) THANKS

Nitin Sontakke · Answer

Case works just fine as shown by Vikas. I am just not sure of syntax he has posted. I am used to using as follows: select sum ( case when ttype in ( 'P' , 'R' , 'T' ) then Qty + QtyFree else 0 end ) as TotalIn , sum ( case when ttype in ( 'S' , 'B' , 'A' ) then Qty + QtyFree else 0 end ) as TotalOut from Detail Group by BId

Gajendra Jangid · Answer

select sum(case when ttype in ('P', 'R', 'T') then Qty + QtyFree else 0 end) as TotalIn ,sum(case when ttype in ('S', 'B', 'A') then Qty + QtyFree else 0 end) as TotalOut from Detail Group by BId

Shaik Ali · Answer

thanks for all i have done the following SELECT BID, SUM( IIF( TTYPE IN ('P','R','T','I') ,(Qty+QtyFree) ,0 )) AS TOT_IN, SUM( IIF(TTYPE IN ('S','B','N','O') ,(Qty+QtyFree) , 0)) AS TOT_OUT From Detail GROUP BY BID

Vikas Gore · Answer

Hi, Try with this SELECT SUM((CASE TTYPE IN ('P','R','T') THEN (Qty+QtyFree) ELSE 0 END) AS TOT_IN, SUM(CASE TTYPE IN ('S','B','A') THEN (Qty+QtyFree) ELSE 0 END) AS TOT_OUT From Detail Group By BID

Vishal Patel · Answer

Hello shaik, Your total InTime and out-time are different where condition so better to use convert in one select statement. Select (SELECT SUM(Qty+QtyFree) AS TOT_IN FROM detail AS D WHERE TTYPE IN ('P','R','T') GROUP BY BID) as Total_IN, (SELECT SUM(Qty+QtyFree) AS TOT_OUT FROM detail WHERE TTYPE IN ('S','B','A') GROUP BY BID) as Total_Out