Skip to main content
L
liquid_One
Inspiring
May 25, 2006
Answered

Performing arithmitic in query. . .

  • May 25, 2006
  • 4 replies
  • 922 views
I have an inventory page that pulls the following information about an each item. . .
inv_id - PK
inv_item - The model number of the item
man_name - Manufacturer
inv_cost - Our cost of the item per unit
inv_price The price we sell the item for
inv_desc - Long description of the item

All that information gets pulled for each record that gets pulled. I want to display a column that will be titled "Margin".
The margin is the percent of profit we make off selling each unit, if the cost is $200, and the price is $349.99, then the margin would be 57.1%. You get the number in the following formula: Margin = Cost \ Price.
I want to calculate this amount for each record and put it into a column named "margin". The reason I want to calculate it before it pulls back to CF is because I want the users to be able to sort by margin.
Im not sure if this is even possible, especially in Access, but I will be switching to SQL server 2000 in about 2 weeks, so if you have a solution in either access or sql server 2000, please post it here. I have a dev version up of sql server to check to see if it works.
Thanks guys,
Mike
    This topic has been closed for replies.
    Correct answer Dan_Bracuk
    The obvious answer works.

    select price, cost, cost/price as margin
    from etc

    4 replies

    L
    liquid_OneAuthor
    Inspiring
    May 25, 2006
    That is exactly what it was. . . .thanks again!
    L
    liquid_OneAuthor
    Inspiring
    May 25, 2006
    Anybody know why I get an error trying to put oreder by margin in the query? and how to work around?
    P
    paross1
    Participating Frequently
    May 25, 2006
    You usually can't order by a column alias, so you would either sort by the column "numeric position" in the SELECT statement (ORDER BY 3, 4, etc.), or repeat the actual calculation that rendered the "margin" in the SELECT (ORDER BY cost/price, etc.)

    Phil
    L
    liquid_OneAuthor
    Inspiring
    May 25, 2006
    Upon further consideration. . . .
    Why wont it let me add "order by margin" at the end of the query, saying I dont have enough parameters?
    Ill prepare myself for another right in front of my face answer. . .
    Thanks,
    Mike
    D
    Dan_BracukCorrect answer
    Inspiring
    May 25, 2006
    The obvious answer works.

    select price, cost, cost/price as margin
    from etc
    P
    paross1
    Participating Frequently
    May 25, 2006
    Just make sure that price is never 0.

    Phil
    L
    liquid_OneAuthor
    Inspiring
    May 25, 2006
    I cant believe I didnt think to try that. . .
    Anyway, it worked perfect, once again you guys have saved the day. . .

    -Mike
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices