CODEWITHSUNDEEP
postgresql
Manish Gupta
Manish Gupta

Reputation: 4666

Postgresql alias for mathematical equation

like this

SELECT
  brand,
  SUM(unicom.amount_total) AS sale,
  COUNT(sale_order.name) AS qty,
  SUM(sale_order.amount_total)*0.15 + COUNT(sale_order_line.name)*65 + (SUM(sale_order.amount_total)*0.15 + COUNT(sale_order_line.name)*65)*0.14
FROM
  sale_order,
  unicommerce
  ......

I want to reduce the complexity in reading queries like above.
Is there any way to use aliases sale and qty in select itself?

I am using version 9.3.6

Upvotes: 0

Views: 793

Answers (3)

Patrick
Patrick

Reputation: 32296

No, you cannot do that. But you can make the calculation less verbose by using table aliases and sub-queries, but that may incur a performance penalty:

SELECT
  brand,
  sum(u.amount_total) AS sale,
  s.qty,
  s.at15 + s.l_num + (s.at15 + s.l_num) * 0.14 AS big_sum
FROM (
  SELECT count(*) AS qty, sum(amount_total)*0.15 AS at15, l.num AS l_num 
  FROM sale_order so
  JOIN (
    SELECT sales_order, count(*) * 65 AS num
    FROM sale_order_line 
    GROUP BY sales_order) l ON l.sales_order = s.id
  GROUP BY name
  ) s
JOIN unicommerce u ON ......

In general, you perform every calculation at the lowest possible level - e.g. where you have access to all required columns, then assign an alias and use that in higher-level queries.

Upvotes: 0

harmic
harmic

Reputation: 30597

Unfortunately you cannot reference SELECT fields in the same SELECT-List via their aliases. You can, however, reference the aliases in an outer query, like this:

SELECT 
    brand,
    sale,
    qty,
    sale*0.15 + qty*65 + (sale*0.15 + qty*65)*0.14  
FROM (
    SELECT
      brand,
      SUM(unicom.amount_total) AS sale,
      COUNT(sale_order.name) AS qty,
    FROM
      sale_order,
      unicommerce
      ......
);

You can also use the aliases in ORDER BY clauses (but not WHERE, ORDER BY or HAVING).

See: select-lists in the manual.

Upvotes: 1

Vao Tsun
Vao Tsun

Reputation: 51599

yes, you can use your initial query as subquery: 

select 
    sale
  , qty
  , sum
from 
(
SELECT
  brand,
  SUM(unicom.amount_total) AS sale,
  COUNT(sale_order.name) AS qty,
  SUM(sale_order.amount_total)*0.15 + COUNT(sale_order_line.name)*65 + (SUM(sale_order.amount_total)*0.15 + COUNT(sale_order_line.name)*65)*0.14
FROM
  sale_order,
  unicommerce
...) SUBQ

or create a view and query a view

Upvotes: 0

Related Questions