I have different category of data in my table. I want to count items in each section of category. Each section contains more than two categories.
How can i optimize this query?
SELECT s1,s2,s3,s4,s5,s6,s7,s8,s9,s10,s11,s12
FROM (
(SELECT count(category) as s1 FROM `category_data` WHERE `type` = '2' and category IN ('2','3','4','5','6','7')) AS t1,
(SELECT count(category) as s2 FROM `category_data` WHERE `type` = '2' and category IN ('8','9','10','11','12')) AS t2,
(SELECT count(category) as s3 FROM `category_data` WHERE `type` = '2' and category IN ('13','14','15','16','17')) AS t3,
(SELECT count(category) as s4 FROM `category_data` WHERE `type` = '2' and category IN ('18','19','20','21','22')) AS t4,
(SELECT count(category) as s5 FROM `category_data` WHERE `type` = '2' and category IN ('23','24','25','26','27')) AS t5,
(SELECT count(category) as s6 FROM `category_data` WHERE `type` = '2' and category IN ('28','29','30','31','32')) AS t6,
(SELECT count(category) as s7 FROM `category_data` WHERE `type` = '2' and category IN ('33','34','35','36','37')) AS t7,
(SELECT count(category) as s8 FROM `category_data` WHERE `type` = '2' and category IN ('38','39','40','41','42')) AS t8,
(SELECT count(category) as s9 FROM `category_data` WHERE `type` = '2' and category IN ('43','44','45','46','47')) AS t9,
(SELECT count(category) as s10 FROM `category_data` WHERE `type` = '2' and category IN ('48','49','50','51','52')) AS t10,
(SELECT count(category) as s11 FROM `category_data` WHERE `type` = '2' and category IN ('53','54','55','56','57')) AS t11,
(SELECT count(category) as s12 FROM `category_data` WHERE `type` = '2' and category >= ('58')) AS t12
)
SELECT
SUM(category IN ('2','3','4','5','6','7')) AS s1,
...
SUM(category >= ('58')) AS s12
FROM
`category_data`
WHERE `type` = '2'
Instead of reading the table multiple times you can do this just once.
I use SUM() instead of count, cause the boolean expression in it equals to true or false, 1 or 0, which is then basically the same as counting.