 <p>Judging from your quote it seems to me you want to do something like this (<a href="http://sqlfiddle.com/#!9/742b39/1/0" rel="nofollow">SQLfiddle</a>):</p> <pre><code>SELECT m.info1, m.info2, COUNT(DISTINCT CONCAT(m.key1, ' ', m.key2)) key_count, GROUP_CONCAT(DISTINCT CONCAT(m.key1, ' ', m.key2) ORDER BY m.key1, m.key2) key_pairs, COUNT(DISTINCT p.serial) serial_count, GROUP_CONCAT(DISTINCT p.serial ORDER BY p.serial) serials, COUNT(DISTINCT p.product_data) data_count, GROUP_CONCAT(DISTINCT p.product_data ORDER BY p.product_data) product_data FROM main_info m INNER JOIN product1 p ON p.key1 = m.key1 AND p.key2 = m.key2 WHERE p.serial &lt; 10 GROUP BY m.info1, m.info2 </code></pre> <p>Count distinct values and list them, is this correct? You can't just group by info1, info2 and also have columns for key1 or key2 in the result (e.g. min(key1) or max(key2) would work). I adjusted this in the query above, although it is quite different from your result it might be what you actually need, maybe with a few changes.</p>