Making SQL query more efficient

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 co开发者_如何学编程lour = '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.

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' )

Depending on how your database handles boolean expressions, this:

select sum(price<10),sum(price between 10 and 20)...  from tab; 

or this

select sum(case when price<10 then 1 else 0 end),sum(case when price between 10 and 20 then 1 else 0 end)...  from tab; 

might help.

SELECT count(*) as products, 

if(price < 10, 'price band 1',
 if (price between 10 and 20, 'price band 2',
  'price band 3'
 )
) as priceband,
  t.colour

from table t
group by t.colour, pricebrand

That will give you

products    colour    priceband
53          red       price band 1
65          red       price band 2
12          blue      price band 1
23          blue      price band 2

etc.