SELECT id HAVING maximum count of id

Question

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?

matt walters · Accepted Answer · 2013-01-10T02:11:25.773

19

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;

edited Jan 10 '13 at 02:11

answered Jan 10 '13 at 02:03

matt walters

571
6
14

You should add a `limit 1` to answer the question. – Gordon Linoff Jan 10 '13 at 02:06
1

@GordonLinoff `limit 1`. what if there are multiple instances having the same *greatest* count? – John Woo Jan 10 '13 at 02:08
3

consider this result, `1, 100`, `2, 100`, `3, 50`. – John Woo Jan 10 '13 at 02:08
@JW Depeneds on the context. maybe you don't care if there are ties for the max... maybe you do... – matt walters Jan 10 '13 at 02:09
I think this would be a logic problem on my end.. what to show. – a coder Jan 10 '13 at 02:09
I need to assign an importance to each color_id and let that be the tie breaker. – a coder Jan 10 '13 at 02:20

John Woo · Answer 2 · 2013-01-10T02:18:58.777

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)

score 2 · Answer 3 · answered Jan 10 '13 at 02:13

2

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

answered Jan 10 '13 at 02:13

drneel

2,796
5
25
42

score 0 · Answer 4 · answered Nov 13 '20 at 13:52

0

To make it simple, use in built function in Oracle(Works only with Oracle Database 11g+ version) :

select stats_mode(color_id) from so_test

This would return highest occurrence of colour ids.

answered Nov 13 '20 at 13:52

Shashi

60
6

SELECT id HAVING maximum count of id

4 Answers4

Linked