how to query with formula?

Question

how to query with formula?

Sharul Nizam

Ten Centuries

Points: 1302
More actions
April 19, 2006 at 10:55 am

#93726

hi,
let say, i've these following table structure and data
company_no | product_status | product_no | no_of_item
--------------------------------------------------------------------------
1204           | new stock        | AB1925      | 34
1204           | new stock        | BB5649      | 45
1204           | sold out           | FG3478      | 21
1204           | carry forward    | LG3756      | 29
1291           | carry forward    | KU7890      | 31
1291           | new stock        | HC8219      | 23
1291           | new stock        | JK1980       | 9
1291           | sold out           | GY7458      | 23
1309           | carry forward    | JI3489       | 78
1309           | carry forward    | KU8790      | 23
1309           | new stock        | GT8769      | 49
1309           | sold out           | JU9589       | 9
formula is as follows:
current product = (carry forward+new stock) - sold out
how to query to get current product for each company? the expected output is as follows:
company_name | current_product
---------------------------------
1204               | 87
1291               | 40
1309               | 141
please help me...

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

untried & untested but should be something like this...

select a.company_no as company_name, (sum(a.no_of_item) - b.sold) as current_product from
myTable a join
(select company_no, no_of_item as sold from myTable
where product_status = 'sold out')b
on a.company_no = b.company_no
group by a.company_no

**ASCII stupid question, get a stupid ANSI !!!**

sushila SSC-Dedicated Points: 35293 More actions · Answer 2

sorry...before grouping you should add a further filter of...

where product_status not like 'sold out'

**ASCII stupid question, get a stupid ANSI !!!**

Sharul Nizam Ten Centuries Points: 1302 More actions · Answer 3

ms. sushila, it's really work. tq very much. may god bless you.