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.