CODEWITHSUNDEEP
plsqlcase
user2633907
user2633907

Reputation: 13

Obtaining a value from 2 different case statements

I have 2 tables. First, a sales table:

 Salesid      Amount     Productid      Monthid
   20           10         1             201307
   15           25         1             201301
   40           20         5             201303

NOTE: More than 1000 rows.

Second, I have a product table:

 Productid     Description       Product_elemid

    1            AAA                  24
    2            BBB                  57           
    5            CCC                  23
    1            AAA_ACE              25

NOTE: About 100 rows.

Now, I want to display some data from these 2 tables. This is what I currently have:

Select p.description 

       case ( when s.monthid between 201301 and 201307 then sum(s.amount) else 0 end) 
       case ( when s.monthid between 201303 and 201305 then sum(s.amount) else 0 end) 
from sales s, product p
where s.productid = p.productid 
and p.productid in ('1, '2', '5')
group by p.description

I get a table with 3 columns:

p.description         case 1 values             case 2 values

So far so good. Now I also want to have another column that gives me the difference of the values from the 2 cases.

Can I write this in a case statement, or is there a better way to do it?

NOTE : Performing a self join here is not desirable for me, as it has a lot of rows and thus will take too long to display.

Thanks.

Upvotes: 0

Views: 87

Answers (1)

Kyle
Kyle

Reputation: 3308

You could make your original statement into a subquery:

Select description, case_1, case_2, case_1 - case_2
from
 (Select p.description as description

   case ( when s.monthid between 201301 and 201307 then sum(s.amount) else 0 end) as case_1
   case ( when s.monthid between 201303 and 201305 then sum(s.amount) else 0 end) as case_2
 from sales s, product p
 where s.productid = p.productid 
 and p.productid in ('1, '2', '5')
 group by p.description
)

Upvotes: 1

Related Questions