how to query with formula and listing by group?
-
hi,
let's say, i've these following data:-
company_no | product_status | product_no | no_of_item | category
----------------------------------------------------------------
1204 | new stock | AB1925 | 34 | food
1204 | new stock | BB5649 | 45 | furniture
1204 | sold out | FG3478 | 21 | food
1204 | sold out | LG3756 | 25 | food
1204 | carry forward | KU7890 | 78 | equipment
1204 | carry forward | JI8940 | 34 | food
1204 | carry forward | GE6457 | 28 | food
1204 | sold out | ry4786 | 12 | furniture
1212 | new stock | AB1125 | 34 | food
1212 | sold out | AB1911 | 23 | food
1212 | new stock | AB1921 | 12 | food
1212 | new stock | AB1325 | 11 | furniture
formula
---------
formula is as follows:
current product = (carry forward+new stock) - sold out
question : how to query (SQL) to get the expected output below?
expected output
---------------
company_no | category | current_product
-----------------------------------------
1204 | food | 50
1204 | furniture | 33
1204 | equipment | 78
1212 | food | 23
1212 | furniture | 11
plz help me
-
To query this information going forward is not recommended. Basically, you are going to have to find the last c/f figures by groups and do the same for new and used stock. As the table grows it will get harder to pick out the most recent c/f figures etc.
My option would be to use a query that acts like a trigger. IE. Populate a new table with the c/f figures to start with into a balancing column. Then as data is inserted into the table, run an update statement dependent upon minus or plus to the balancing figure. You will be left with a neat table that can contain history if you log that as well.
If possible, get the owner of the base table to populate a balancing column on the source using a trigger and it will make life a lot easier for you.
------------------------------
The Users are always right - when I'm not wrong! -
This query will work:
select company_no
,category
,current_product = sum(Case When product_status = 'sold out' Then -(no_of_items) Else no_of_items End)
from test
group by company_no
,category
-
Mr. Joe Williams tq very much for that solutions. It's really work.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply