asked on

ORDER BY a multiplication result

Hi all, I was just wondering if you can ORDER BY a multiplication result..

eg.
In my database I have 2 figures...
Price = 1800,2000,2100
Conversion = 1.72,1.54,1.64
Output to website = 3096, 3080, 3444

If I order by price;
3096
3080
3444

if I order by conversion;
3080
3444
3096

My desired outcome is to order by the outcome;
3080
3096
3444

Is there a way to do this within a statement?

Set Outcome=oConn.Execute("SELECT * FROM product WHERE prod_live=TRUE ORDER BY output") ???

The reason is so we when we buy from multiple suppliers overseas (from different currencies) we can order them in the customers desired Currency lowest to highest or vv.

Ryan Chong

if you don't have a field for outcome, then you can try:

Set Outcome=oConn.Execute("SELECT * FROM product WHERE prod_live=TRUE ORDER BY price*conversion ")

Graeme McGilvray

ASKER

Hi Ryan, I will test this and get back to you. Thank you

Dave Baldwin

I think you have to make "price*conversion" be a field in the output to ORDER BY it. Something like...

SELECT *, (price*conversion) AS Result FROM product WHERE prod_live=TRUE ORDER BY Result

Open in new window

Graeme McGilvray

ASKER

Hi Dave, I will test this and get back to you. Thank you

Juan Carlos

Better create a view that you can use the calculated data for different use cases.

create view products as
   select p.*,
      p.price * p.conversion as priceconv
   from product p
   where p.prod_live = TRUE;

Open in new window

then you simply use the view as any table, example:

select * from products order by priceconv;
select * from products order by priceconv desc;
Select * from products where priceconv between 3000 and 5000 order by priceconv, prod_name;
Set Outcome=oConn.Execute("SELECT * FROM products ORDER BY priceconv")

Open in new window

ste5an

Just some comments;

I hope your sample is not reflecting your real database structure. Cause Product should be independent from Supplier. Thus the Conversion column seems to belong to the Supplier.

Instead of prod_live=TRUE you should have a "InStock" or "InSale" or "InProduction" table, where you track this state by date from and date to columns.

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.