mysql - SQL query for total unique hits -

i'm struggling write correct sql statement yield desired result normalized tables.

i have:

table: sites id, url 1, google.com 2, facebook.com  table: hits id, site_id 1, 1 2, 1 3, 1 4, 2

i'd write sql statement produce following:

site_id, total_hits 1, 3 2, 1

this show count of how many times each url has been hit. appreciated!

select s.site_id, count(*) totalhits sites s join hits h on s.site_id = h.site_id group s.site_id

you should group by on site id , count number of hits.

Core code