postgresql mathematical formula error

Question

Hi I am trying to use a mathematical function on each row in postgresql. But It gives me a error.
My Query:

Select
  stock_inventory_line.product_code AS Sku,
  COUNT(sale_order_line.name) AS Qty_Sold,
  stock_inventory_line.product_qty AS Current_Qty,
  (stock_inventory_line.product_qty / Qty_Sold) AS NOM
From
  sale_order_line,
  product_product,
  product_template,
  product_category,
  stock_inventory_line
WHERE
  sale_order_line.product_id = product_product.id AND
  product_product.product_tmpl_id = product_template.id AND
  product_template.categ_id = product_category.id AND
  product_product.default_code = stock_inventory_line.product_code
GROUP BY
  Sku,
  Current_Qty,
  NOM;

On this Query It gives me a error: column qty_sold doesn't exist. If i change the 5th line to

(stock_inventory_line.product_qty / COUNT(sale_order_line.name)) AS NOM

It gives me an error: Aggregate functions not allowed in group by.

Sami Kuhmonen · Accepted Answer · 2015-06-16 11:30:36Z

1

You are trying to use COUNT(sale_order_line.name) as a group by item. Aggreagte functions work on grouped item. They are not for grouping them.

I do not know your tables but try

Select
  stock_inventory_line.product_code AS Sku,
  COUNT(sale_order_line.name) AS Qty_Sold,
  stock_inventory_line.product_qty AS Current_Qty,
  (stock_inventory_line.product_qty / COUNT(sale_order_line.name)) AS NOM
From
  sale_order_line,
  product_product,
  product_template,
  product_category,
  stock_inventory_line
WHERE
  sale_order_line.product_id = product_product.id AND
  product_product.product_tmpl_id = product_template.id AND
  product_template.categ_id = product_category.id AND
  product_product.default_code = stock_inventory_line.product_code
GROUP BY
  stock_inventory_line.product_code,
  stock_inventory_line.product_qty;

Basically I remove NOM from the GROUP BY. It is a product for each group, not something you group by.

edited Jun 16, 2015 at 11:30

Sami Kuhmonen

31.4k9 gold badges67 silver badges81 bronze badges

answered Jun 16, 2015 at 11:16

Nuri Tasdemir

9,8823 gold badges47 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Manish Gupta Over a year ago

So how do i use it in the formula? i want the result of division of Current_Qty and Qty_Sold.

Nuri Tasdemir Over a year ago

I give a new select however I am not sure this is what you want.

Manish Gupta Over a year ago

Not working. Still giving me a Error column qty_sold doesn't exist

Nuri Tasdemir Over a year ago

Try this one. I changed the usage in definition of NOM.

keberwein · Accepted Answer · 2015-06-16 20:59:27Z

I run into problems like this all the time. Using a CTE always works for me. Do your aggregations inside the CTE then when you call them in the outer statement, Postgres sees them as numeric instead of aggregations. Your query might run a bit faster too!

Example:

WITH cte AS( 
  SELECT
  sale_order_line.product_id,
  stock_inventory_line.product_code,
  stock_inventory_line.product_code AS Sku,
  COUNT(sale_order_line.name) AS Qty_Sold,
  stock_inventory_line.product_qty AS Current_Qty,
  (stock_inventory_line.product_qty / Qty_Sold) AS NOM
  FROM
  sale_order_line,
  stock_inventory_line)

SELECT Sku, Qty_Sold, Current_Qty, NOM
FROM 
cte,
product_product,
product_template,
product_category

WHERE
  cte.product_id = product_product.id AND
  product_product.product_tmpl_id = product_template.id AND
  product_template.categ_id = product_category.id AND
  product_product.default_code = cte.product_code
GROUP BY
  Sku,
  Current_Qty,
  NOM;

Collectives™ on Stack Overflow

postgresql mathematical formula error

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related