1
SELECT .......

---a long calculation-- AS MARGIN,

---calculation--- AS SALES

FROM          .....
where .....
GROUP BY .....
order by REV desc;

Now i want to add another column which is "Margin percent" which is MARGIN/SALES. How can i do it without calculating MARGIN and SALES again ? so the output table will be:

| Margin | SALES | Margin percent
jarlh
  • 42,561
  • 8
  • 45
  • 63
David Gabbay
  • 107
  • 8
  • [using a calculated column in the same query](https://stackoverflow.com/questions/8840228/postgresql-using-a-calculated-column-in-the-same-query) -- CROSS APPLY/LATERAL if not supported then wrapping with subquery will help – Lukasz Szozda Dec 16 '18 at 20:44
  • Need to know what database you use "sql" comes in many flavours, each dbms vendor has it's own recipe. You might be able to use a window function if the dbms supports it, or `cross apply` or `lateral join`, all depending on which dbms – Paul Maxwell Dec 17 '18 at 02:41
  • i"m using ms sql – David Gabbay Dec 17 '18 at 07:10

1 Answers1

2

Wrap your current query up in a derived table (i.e. the subquery). Do the percent calculation on its result. 

select margin, sales, margin * 100.0 /sales
from
(   
    SELECT .......

    ---a long calculation-- AS MARGIN,

    ---calculation--- AS SALES

    FROM          .....
    where .....
    GROUP BY .....
) dt
jarlh
  • 42,561
  • 8
  • 45
  • 63
  • _dt_ is just a table alias. Shouldn't be needed, but some products require it. You can try without it, or chose another table alias. – jarlh Dec 17 '18 at 07:19