SELECT id HAVING maximum count of id

SELECT color_id
FROM
    (
        SELECT  color_id, COUNT(color_id) totalCount
        FROM    products 
        WHERE   item_id = 1234 
        GROUP   BY color_id 
    ) s
HAVING totalCount = MAX(totalCount)

UPDATE 1

SELECT  color_id, COUNT(color_id) totalCount
FROM    products 
WHERE   item_id = 1234 
GROUP   BY color_id 
HAVING  COUNT(color_id) =
(
  SELECT  COUNT(color_id) totalCount
  FROM    products 
  WHERE   item_id = 1234 
  GROUP   BY color_id 
  ORDER BY totalCount DESC
  LIMIT 1  
)
  • SQLFiddle Demo (having tie)

SELECT color_id AS id, COUNT(color_id) AS count 
FROM products 
WHERE item_id = 1234 AND color_id IS NOT NULL 
GROUP BY color_id 
ORDER BY count DESC
LIMIT 1;

This will give you the color_id and the count on that color_id ordered by the count from greatest to least. I think this is what you want.


for your edit...

SELECT color_id, COUNT(*) FROM products WHERE color_id = 3;