Simply count one-to-many

Question

I am trying to count the number of hits per country in my database, which has the following tables:

mysql> use DB; show tables;
+--------------+
| Tables_in_DB |
+--------------+
| Hits         |
| Tags         |
| HitTags      |
+--------------+

And here are some example columns from those tables:

mysql> select * from Hits;
+---------+----------+--------------+
| HitID   | HitDate  | HitText      |     
+---------+----------+--------------+
| 0123456 | 01/01/12 | access page1 |
| 7890123 | 01/02/12 | access page2 |
| 4567890 | 01/03/12 | access page3 |
+---------+----------+--------------+

mysql> select * from Tags;
+-----------+---------+---------+
| TagID     | TagType | TagText |     
+-----------+---------+---------+
| 123123213 | country | USA     |
| 456456456 | country | Brazil  |
| 789789789 | country | China   |
| 012345678 | city    | London  |
+-----------+---------+---------+

mysql> select * from HitTags;
+---------+-----------+
| HitID   | TagID     |
+---------+-----------+
| 0123456 | 123123213 |
| 7890123 | 456456456 |
| 4567890 | 789789789 |
+---------+-----------+

I want to fetch the number of hits per country, such as in the following SQL query {and pseudo code}:

SELECT DISTINCT TagText, TagID FROM Tags
    WHERE TagType = 'country';

for each $row {
    SELECT COUNT(HitID) As HitCount FROM HitTags
        WHERE HitTags.TagID = $row[Tags.TagID];
}

I have no issue doing this when using separate queries, but I am sure there is a way of doing this more elegantly (i.e. using a single SQL query instead of one-per-country).

Maybe using a JOIN, maybe using the ANY keyword. Or maybe using some nested SELECT statements?

Here is what I have tried:

SELECT COUNT(HitID) AS HitCount, TagText FROM HitTags
    JOIN Tags ON Tags.TagID =
    ANY (SELECT TagID FROM Tags WHERE TagType = 'country');

But that returns a single row with the total of all hits (from everywhere, although a country is listed along with the result).

Ideally, the result would be something like:

+---------+----------+
| TagText | HitCount |
+---------+----------+
|     USA |     1234 |
|  Brazil |     5678 |
|   China |     9012 |
+---------+----------+

I know this must be quite common (as in "yeah, I saw this in MySQL class 101")...

I searched the web a lot, but everybody seems to be using a single table (instead of three). I'm really no good with those "one-to-many" relationships. Sorry to be such a newbie!

Any idea, hint or advice would be useful, thanks in advance!

Answer 1

You are just missing a GROUP BY clause:

SELECT 
  COUNT(HitID) AS HitCount,
  TagText 
FROM HitTags
  JOIN Tags ON Tags.TagID =
    ANY (SELECT TagID FROM Tags WHERE TagType = 'country')
GROUP BY TagText;

Actually, this is better with a LEFT JOIN:

SELECT
  COUNT(HitID) AS HitCount,
  TagText
FROM 
  Tags 
  LEFT JOIN HitTags ON Tags.TagID = HitTags.TagID
GROUP BY TagText

Answer 2

Try this:

SELECT *, (SELECT COUNT(HitID) FROM HitTags WHERE TagID = t.TagID ) AS hit_count 
FROM Tags t WHERE TagType = 'country'

This would do the same but I think is faster.