SQL query efficiency

Question

I have an working query and not sure if its efficient or not.

Table columns:

p_type: can be STRING sell, cancelsell, bank, cancelbank
id
fid
unit

Operation to be performed:

SUM(unit) for each process type sell,cancelsell,bank,cancelbank.
SUM(UNIT for sell) - SUM(unit for cancelsell) , SUM(unit for bank) - SUM(unit for cancelbank)

NOTE: I am also checking for id and fid in each select though I need to check with same values for all select.

The existing query:

    select sell-cancelsell scount, bank-cancelbank bcount from

    (select sum(a.unit) as sell from table1 a where   
    a.p_type = 'Sell' and a.id=1 and a.fid=2 ),

    (select sum(c.unit) as cancelsell from table1  c where      
    c.p_type = 'CancelSell' and c.id=1 and c.fid=2),

    (select sum(b.unit) as bank from table1  b where     
    b.p_type = 'Bank' and b.id=1 and b.fid=2),

    (select sum(d.unit) as cancelbank from table1  d where  
    d.p_type = 'CancelBank' and d.id=1 and d.fid=2)

Is good enough? If anyone can suggest a way to make it more efficient that would be great.

rs. · Accepted Answer · 2012-09-28 13:22:19Z

5

You can do this

select 
sum(Case when a.p_type = 'sell' then a.unit else null end) as sellUnit, 
sum(Case when a.p_type = 'CancelSell' then a.unit else null end) as CancelSellUnit,
sum(Case when a.p_type = 'Bank' then a.unit else null end) as BankUnit ,
sum(Case when a.p_type = 'CancelBank' then a.unit else null end) as CancelBankUnit  
from table1 a where and a.id=1 and a.fid=2

answered Sep 28, 2012 at 13:22

rs.

27.6k13 gold badges71 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MRSGT _GT Over a year ago

@rs. thank you very much. I knew that I had to use Case, but was not getting it quite right.thank you again

Joe G Joseph · Accepted Answer · 2012-09-28 13:34:30Z

2

try this:

SELECT 
        SUM(CASE WHEN P_TYPE = 'SELL' THEN UNIT END) - 
        SUM(CASE WHEN P_TYPE = 'CANCELSELL' THEN UNIT END) AS SCOUNT,
        SUM(CASE WHEN P_TYPE = 'BANK' THEN UNIT END) -
        SUM(CASE WHEN P_TYPE = 'CANCELBANK' THEN UNIT END) AS BCOUNT 
FROM    TABLE1  
WHERE   ID=1 AND FID=2

answered Sep 28, 2012 at 13:34

Joe G Joseph

24.2k5 gold badges58 silver badges58 bronze badges

1 Comment

MRSGT _GT Over a year ago

Thank you. I actually used CASE this way ly!

Seasoned · Accepted Answer · 2012-09-28 13:51:03Z

0

select temp1.sumunit - temp2.sumunit,temp3.sumunit-temp4.sumunit from
(select p_type,sum(unit) as sumunit from table1
group by p_type) as temp1
inner join
(select p_type,sum(unit) as sumunit from table1
group by p_type) as temp2 on temp2.p_type = 'cancelsell' 
inner join
(select p_type,sum(unit) as sumunit from table1
group by p_type) as temp3 on temp3.p_type = 'bank'
inner join
(select p_type,sum(unit) as sumunit from table1
group by p_type) as temp4 on temp4.p_type='cancelbank'
where temp1.p_type='sell'
and temp1.id = 1 and temp1.fid = 2
and temp2.id = 1 and temp2.fid = 2
and temp3.id = 1 and temp3.fid = 2
and temp4.id = 1 and temp4.fid = 2

answered Sep 28, 2012 at 13:51

Seasoned

1,0591 gold badge8 silver badges19 bronze badges

Collectives™ on Stack Overflow

SQL query efficiency

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related