 <p>You can use <code>case</code> and <code>sum</code> like;</p> <pre><code>SELECT t1.Date, sum(case when t2.country = 'Saudi Arabia' then 1 else 0 end) `Saudi Arabia`, sum(case when t2.country = 'Kuwait' then 1 else 0 end) `Kuwait`, sum(case when t2.country in ('Saudi Arabia', 'Kuwait') then 0 else 1 end) others FROM Online_customer_activity_v2 t1 JOIN `players` t2 ON t1.`Customers` = t2.`username` GROUP BY t1.Date </code></pre> <p>You can use <code>if</code> like,</p> <pre><code>SELECT t1.Date, sum(if(t2.country = 'Saudi Arabia', 1, 0)) `Saudi Arabia`, sum(if(t2.country = 'Kuwait', 1, 0)) `Kuwait`, sum(if(t2.country in ('Saudi Arabia', 'Kuwait'), 0, 1)) others FROM Online_customer_activity_v2 t1 JOIN `players` t2 ON t1.`Customers` = t2.`username` GROUP BY t1.Date </code></pre>