Is it possible to combine these three join queries into one? I would like to combine the counts of the stats by different hit_types into one query. Is that possible?

MariaDB [db]&gt; select allurls.id, count(s1.id) from allurls inner join stats s1 on allurls.id = s1.allurl_id and s1.hit_type = 0 where s1.hit_date &gt;= '2018-01-15'  group by allurls.id;
+-----+--------------+
| id  | count(s1.id) |
+-----+--------------+
| aaa |            1 |
| cnn |           16 |
+-----+--------------+

MariaDB [db]&gt; select allurls.id, count(s1.id) from allurls inner join stats s1 on allurls.id = s1.allurl_id and s1.hit_type = 1 where s1.hit_date &gt;= '2018-01-15'  group by allurls.id;
+-----+--------------+
| id  | count(s1.id) |
+-----+--------------+
| cnn |            1 |
+-----+--------------+

MariaDB [db]&gt; select allurls.id, count(s1.id) from allurls inner join stats s1 on allurls.id = s1.allurl_id and s1.hit_type = 2 where s1.hit_date &gt;= '2018-01-15'  group by allurls.id;
+-----+--------------+
| id  | count(s1.id) |
+-----+--------------+
| cnn |            4 |
+-----+--------------+


I tried to combine the first two but the numbers are all messed up and it eliminated the first result 'aaa'.

MariaDB [db]&gt; select allurls.id, count(s1.id), count(s2.id) from allurls inner join stats s1 on allurls.id = s1.allurl_id and s1.hit_type = 0 inner join stats s2 on allurls.id = s2.allurl_id and s2.hit_type = 1 where s1.hit_date &gt;= '2018-01-15' and s2.hit_date &gt;= '2018-01-15' group by allurls.id;
+-----+--------------+--------------+
| id  | count(s1.id) | count(s2.id) |
+-----+--------------+--------------+
| cnn |           16 |           16 |
+-----+--------------+--------------+


I expected to see

+-----+--------------+--------------+
| id  | count(s1.id) | count(s2.id) |
+-----+--------------+--------------+
| aaa |            1 |            0 |
| cnn |           16 |            1 |
+-----+--------------+--------------+


Ultimately I want to also include count(distinct(s4.source_id)). 

Here is a fiddle: https://www.db-fiddle.com/f/tGP5SbC2AdGgeEwWTAgobf/0
Not sure if there is anything that I miss here, but you can count conditionally like:

select allurls.id
    , count(case when s1.hit_type = 0 then 1 end) 
    , count(case when s1.hit_type = 1 then 1 end) 
    , count(case when s1.hit_type = 2 then 1 end) 
    , count(distinct s1.source_id)
from allurls 
join stats s1 
    on allurls.id = s1.allurl_id 
where s1.hit_date &gt;= '2018-01-15' 
  and s1.hit_type between 0 and 2 
group by allurls.id;

 id     c1  c2  c3  c4
aaa     1   0   0   1
cnn     16  1   4   3


Some things to note: 

I moved hit_type predicate to where, it does not make a logical difference, but I prefer to have only the relationship between tables in the join clause if possible.

distinct is not a function, count(distinct(s1.source_id)) is confusing. Better use count(distinct s1.source_id)