SELECT id HAVING maximum count of id
73,713
Solution 1
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;
Solution 2
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
)
Solution 3
SELECT
color_id,
COUNT(color_id) AS occurances
FROM so_test
GROUP BY color_id
ORDER BY occurances DESC
LIMIT 0, 1
Here is a sample fiddle with a basic table that shows it working: sql fiddle
Comments
-
a coder almost 2 years
Have a products table with item_id and color_id. I'm trying to get the color_id with the most non-null instances.
This fails:
SELECT color_id FROM products WHERE item_id=1234 GROUP BY item_id HAVING MAX(COUNT(color_id))
with
Invalid use of group function
This
SELECT color_id, COUNT(color_id) FROM products WHERE item_id=1234 GROUP BY item_id
Returns
color_id count 1, 323 2, 122 3, 554
I am looking for color_id 3, which has the most instances.
Is there a quick and easy way of getting what I want without 2 queries?
-
Gordon Linoff over 11 yearsYou should add a
limit 1
to answer the question. -
John Woo over 11 years@GordonLinoff
limit 1
. what if there are multiple instances having the same greatest count? -
John Woo over 11 yearsconsider this result,
1, 100
,2, 100
,3, 50
. -
matt walters over 11 years@JW Depeneds on the context. maybe you don't care if there are ties for the max... maybe you do...
-
a coder over 11 yearsI think this would be a logic problem on my end.. what to show.
-
a coder over 11 yearsI need to assign an importance to each color_id and let that be the tie breaker.
-
VishalParkash almost 3 yearsfantastic, this is what I was looking for,