CODEWITHSUNDEEP
postgresql
Manish Gupta
Manish Gupta

Reputation: 4666

postgresql mathematical formula error

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.

Upvotes: 0

Views: 41

Answers (2)

keberwein
keberwein

Reputation: 536

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;

Upvotes: 0

Nuri Tasdemir
Nuri Tasdemir

Reputation: 9842

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.

Upvotes: 1

Related Questions