SELECT DISTINCT color_idx.value, COUNT(color_idx.entity_id) AS count FROM catalog_product_entity AS e LEFT JOIN searchindex_result_mage_catalog_product AS tmp_table ON (tmp_table.product_id=e.entity_id) LEFT JOIN cataloginventory_stock_item AS _inventory_table ON _inventory_table.product_id = e.entity_id INNER JOIN catalog_product_index_price AS price_index ON price_index.entity_id = e.entity_id AND price_index.website_id = ‘1’ AND price_index.customer_group_id = 0 INNER JOIN catalog_category_product_index AS cat_index ON cat_index.product_id=e.entity_id AND cat_index.store_id=’1′ AND cat_index.visibility IN(3,Read more