Selecting nested common attributes

I'm using the following statement to query my db and return the most common color, style of item a. Once I get that data, I would then like to select the most common size.

select color, style, size
from orders
where item = 'item a'
group by color, style
order by count(*) desc
limit 1

If I group by color, style, size, it won't count the number of color, style properly. How might开发者_运维技巧 I get this last piece of info from the db?

To be honest, it's hard to see how one query would be more efficient than three. But if you have to, you can merge them into a single query with a union:

select  'Common Color' as Id
,       (select color from orders where item = 'item a' group by color 
           order by count(*) desc limit 1) as Value
union all
select  'Common Style'
,       (select style from orders where item = 'item a' group by style 
            order by count(*) desc limit 1)
union all
select  'Common Size'
,       (select size from orders where item = 'item a' group by size 
            order by count(*) desc limit 1)

The subqueries are there to remove any ambiguity about wether the limit applies to the entire union or just one part.

Use a second query to get the most common size