 <p>One approach is two stages of aggregation</p> <pre><code>select browser, os, version, count(*) from (select order_id, max(case when `key` = 'browser' then `value` end) as browser, max(case when `key` = 'os' then `value` end) as os, max(case when `key` = 'version' then `value` end) as version from order_params op group by order_id ) p group by browser, os, version </code></pre> <p>If you actually want the string that you have, you can concat things together:</p> <pre><code>select concat(coalesce(concat('browser ', browser), ''), coalesce(concat('os ', os), ''), coalesce(concat('version ', version), ''), count(*) from (select order_id, max(case when `key` = 'browser' then `value` end) as browser, max(case when `key` = 'os' then `value` end) as os, max(case when `key` = 'version' then `value` end) as version from order_params op group by order_id ) p group by os, browser, version </code></pre>