Loading

Converting two Select Querys into One Query

Converting two Select Querys into One Query

Shaik Ali

7y897Learn iOS Programming

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

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke7y ago
Accepted 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 Jangid7y ago
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 Ali7y ago
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 Gore7y ago
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 Patel7y ago
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