Answer the question
In order to leave comments, you need to log in
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? 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
Answer the question
In order to leave comments, you need to log in
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 questionAsk a Question
731 491 924 answers to any question