Is it possible to combine these three join queries into one?

Question

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

MariaDB [db]> 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 >= '2018-01-15'  group by allurls.id;
+-----+--------------+
| id  | count(s1.id) |
+-----+--------------+
| aaa |            1 |
| cnn |           16 |
+-----+--------------+

MariaDB [db]> 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 >= '2018-01-15'  group by allurls.id;
+-----+--------------+
| id  | count(s1.id) |
+-----+--------------+
| cnn |            1 |
+-----+--------------+

MariaDB [db]> 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 >= '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]> 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 >= '2018-01-15' and s2.hit_date >= '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

Answer 1

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 >= '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)

Answer 2

This might help, (at least partially):

SELECT allurls.id, COUNT(s1.id)
FROM allurls 
INNER JOIN stats s1 ON allurls.id = s1.allurl_id 
AND (s1.hit_type = 0 OR s1.hit_type = 1 OR s1.hit_type = 2) 
WHERE s1.hit_date >= '2018-01-15'  
GROUP by allurls.id;

Result:

 id  COUNT(s1.id)
aaa             1
cnn            21

The db-fiddle is available here and the numbers appear correct in that they add up to the counts in the first 3 queries (individual - no OR). I'm giving a +1 to this question for providing a fiddle - as recommended here - if only all posters would do the same (sigh...)

I saw no s2, nor an s4, in the fiddle - maybe it needs to be expanded?

Answer 3

OK I found a way with sub-queries (they fix everything), but I'm not sure this is the most efficient.

MariaDB [db]> SELECT allurls.id,
  (SELECT count(*) FROM stats WHERE allurl_id = allurls.id AND hit_type = 0 AND hit_date > '2018-01-15') AS c1,
  (SELECT count(*) FROM stats WHERE allurl_id = allurls.id AND hit_type = 1 AND hit_date > '2018-01-15') AS c2,
  (SELECT count(*) FROM stats WHERE allurl_id = allurls.id AND hit_type = 2 AND hit_date > '2018-01-15') AS c3,
  (SELECT count(DISTINCT( source_id )) FROM stats WHERE allurl_id = allurls.id AND hit_date > '2018-01-15') AS c4
FROM   allurls
HAVING c1 > 0; 

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