Making SQL query more efficient

Question

How can I make this SQL query more efficient?

SELECT 
  (SELECT COUNT(*) FROM table WHERE price < 10) AS priceUnder10,
  (SELECT COUNT(*) FROM table WHERE price BETWEEN 10 AND 20) AS price10to20,
  (SELECT COUNT(*) FROM table WHERE price > 20) AS priceOver20,
  (SELECT COUNT(*) FROM table WHERE colour = 'Red') AS colourRed,
  (SELECT COUNT(*) FROM table WHERE colour = 'Green') AS colourGreen,
  (SELECT COUNT(*) FROM table WHERE colour = 'Blue') AS colourBlue;

I already have indexes on the price and colour columns, so I am looking for a better way to aggregate the data.

I have looked into using GROUP BY, HAVING, self-joins and window functions, but cannot work out how to achieve the same result.

Any suggestions much appreciated.

Answer 1

SELECT 
       COUNT(CASE WHEN price < 10 THEN 1 END) AS priceUnder10,
       COUNT(CASE WHEN price BETWEEN 10 AND 20 THEN 1 END) AS price10to20,
       COUNT(CASE WHEN price> 20 THEN 1 END) AS priceOver20,
       COUNT(CASE WHEN colour = 'Red' THEN 1 END) AS colourRed,
       COUNT(CASE WHEN colour = 'Green' THEN 1 END) AS colourGreen,
       COUNT(CASE WHEN colour = 'Blue' THEN 1 END) AS colourBlue
from YourTable  
WHERE price IS NOT NULL OR  colour IN ('Red','Green','Blue' )