MySQL: Find categories of products whose total price is neither maximum nor minimum?

Question

My data:

product Table:
Category_ID Product_ID Price
1           12         120
1           19         234
2           10         129
3           34         145
3           11         100
4           8          56

I would like to find categories whose total price is neither maximum nor minimum using MySQL.

Results:

Category_ID Total_Price
2           129
3           245

I have found this using the following query, but I would like to know if there is any efficient and better query.

SELECT P.Category_ID, SUM(P.Price) AS Total_Price 
FROM Product P
GROUP BY P.Category_ID
HAVING SUM(P.Price) 
NOT IN
(
(SELECT MAX(Total) FROM (SELECT SUM(Price) AS Total
FROM Product GROUP BY Category_ID) AS T1),

(SELECT MIN(Total) FROM (SELECT SUM(Price) AS Total
FROM Product GROUP BY Category_ID) AS T2)
)

Thank you.

Answer 1

If you are running MySQL 8.0, you can use window functions to rank the categories by ascending and descending price, then filter:

select *
from (
    select category_id, sum(price) as sum_price,
        rank() over(order by sum(price)) rn_asc,
        rank() over(order by sum(price) desc) rn_desc
    from product p
    group by category_id
) p
where rn_asc > 1 and rn_desc > 1

In earlier versions, one alternative uses subqueries:

select category_id, sum(price) as sum_price
from product p
group by category_id
having sum(price) > (select sum(price) from product group by category_id order by sum(price) limit 1)
   and sum(price) < (select sum(price) from product group by category_id order by sum(price) desc limit 1)

This query would benefit an index on (category_id, price).