Improving SQL query performance

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 in the columns priceand colour, therefore, I am looking for the best way to aggregate data.

I have reviewed the use of GROUP BY, HAVINGand independent connections and window functions, but can not decide how to achieve the same result.

Any suggestions that are very 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' )

Answer 2

Depending on how your database processes logical expressions, these are:

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

or

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; 

may I help.

Answer 3

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

It 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

and etc.