I am trying to calculate the number of hits for each country in my database, which has the following tables:
mysql> use DB; show tables; +--------------+ | Tables_in_DB | +--------------+ | Hits | | Tags | | HitTags | +--------------+
And here are some examples of columns from these 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 get the number of hits by country, for example, 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 problem with this when using separate queries, but I'm sure there is a way to do this more elegantly (i.e. using one SQL query instead of one country).
Perhaps using JOIN, perhaps using the ANY keyword. Or maybe with some nested SELECT statements?
Here is what I tried:
SELECT COUNT(HitID) AS HitCount, TagText FROM HitTags JOIN Tags ON Tags.TagID = ANY (SELECT TagID FROM Tags WHERE TagType = 'country');
But this returns a single row with the total number of all hits (from everywhere, although the country is listed along with the result).
Ideally, the result would look something like this:
+---------+----------+ | TagText | HitCount | +---------+----------+ | USA | 1234 | | Brazil | 5678 | | China | 9012 | +---------+----------+
I know that this should be fairly common (as in "yes, I saw it in MySQL 101") ...
I searched a lot on the Internet, but everyone seems to use one table (instead of three). I am not very good at this one-to-many relationship. Sorry to be such a newbie!
Any idea, hint or advice would be helpful, thanks in advance!