2

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

2

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)

2
  • Wow I didn't realize you can use case within count. So count only counts non-null values? I thought it counts all rows regardless of value. – Chloe May 18 '18 at 17:01
  • COUNT(*) or COUNT(1), where 1 being any value count number of rows (even rows where every column is null). COUNT(x), where x being a variable count rows where x is not null. – Lennart May 18 '18 at 17:27
0

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?

1
  • But how does that help? It just sums all counts. There are only 3 values: 0, 1, 2. Same as leaving hit_type out. How do I find the count of each individual hit_type? s2 is in the 4th query. I just tried to join stats table twice. For s4, I tried (not shown) to join stats table 4 times, one time for each value (3 hit_types and distinct(sources)). If I learn the pattern for just 2 hit_types, then I can probably extrapolate to all 4 values. – Chloe May 17 '18 at 0:56
0

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 |
+-----+------+------+------+------+

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.