R
R
rancerenly2022-02-28 20:18:11
PostgreSQL
rancerenly, 2022-02-28 20:18:11

How to merge 2 queries into 1 in Postgresql or how to change below query?

Print the most expensive and cheapest purchase:

SELECT PIP.purchase_id, SUM(PIP.product_count * PR.new_price) AS all_sum
  FROM product_in_purchase AS PIP
    RIGHT OUTER JOIN price_register AS PR ON PIP.product_id = PR.product_id
    GROUP BY PIP.purchase_id
    ORDER BY SUM(PIP.product_count * PR.new_price) DESC LIMIT 1
  UNION [ALL]
SELECT PIP.purchase_id, SUM(PIP.product_count * PR.new_price) AS all_sum
  FROM product_in_purchase AS PIP
    RIGHT OUTER JOIN price_register AS PR ON PIP.product_id = PR.product_id
    GROUP BY PIP.purchase_id
    ORDER BY SUM(PIP.product_count * PR.new_price) LIMIT 1;
Can you tell me how to build the query correctly?
There is something like this, but because of the fields in the database, this solution is not suitable:
SELECT 
  res.purchase_id,
  res.rnk_max,
  res.rnk_min
FROM (
    SELECT 
        pp.purchase_id,
        DENSE_RANK() OVER(ORDER BY pp.product_price * pp.product_count DESC) AS rnk_max,
      DENSE_RANK() OVER(ORDER BY pp.product_price * pp.product_count) AS rnk_min
      FROM public."Product_In_Purchase" AS pp
   ) AS res
WHERE res.rnk_max = 1 OR res.rnk_min = 1

Here are the fields in the tables:
621d030725239644027455.png621d030f983b0106749117.png

Answer the question

In order to leave comments, you need to log in

1 answer(s)
A
Akina, 2022-02-28
@rancerenly

Purely technically, without understanding the essence of what is happening:

WITH cte AS (
    SELECT PIP.purchase_id, SUM(PIP.product_count * PR.new_price) AS all_sum
    FROM product_in_purchase AS PIP
    RIGHT OUTER JOIN price_register AS PR ON PIP.product_id = PR.product_id
    GROUP BY PIP.purchase_id
)
( SELECT * FROM cte ORDER BY all_sum ASC LIMIT 1 )
UNION 
( SELECT * FROM cte ORDER BY all_sum DESC LIMIT 1 )

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question