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?
And the short answer to the above question is, no. You can't. It is not possible to nest aggregate functions.
To find the maximum value of a column, use the MAX() aggregate function; it takes a column name or an expression to find the maximum value. In our example, the subquery returns the highest number in the column grade (subquery: SELECT MAX(grade) FROM student ).
In this case, COUNT(id) counts the number of rows in which id is not NULL .
To get one row with the highest count, you can use ORDER BY ct LIMIT 1 : SELECT c. yr, count(*) AS ct FROM actor a JOIN casting c ON c. actorid = a.id WHERE a.name = 'John Travolta' GROUP BY c.
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;
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
)
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With