MySql Join Tables With Sum Of A Column

I have 3 tables in total

1. category with columns category_id and category_name
2. server with columns server_id, category_id, server_name and server_url
3. server_hit with columns id, server_id, hit_count, day_date

Here in the server_hit table, I am storing how many times a web server is being accessed daily. And in the day_date column I am stroing date in this format YYYYMMDD where YYYY is the four digit year number, MM is the two digit month number & DD is the two digit date number.

While inserting data in the server_hit table, if no row has been created yet with same date then first it creates a row and then insert data in the table.

Now what I want is a list of all the servers from the server table with the following columns. If no row exists in the server_hit table, then total_hit_count should be set to 0 (zero).

1. server_id from server table
2. server_name from server table
3. server_url from server table
4. category_id from server table
5. category_name from category table with condition server.category_id = category.category_id
6. total_hit_count from the server_hit which is the sum of all the hit_count column of a particular server id.

How can I get the list like I described above? I have tried with GROUP BY but I could not make it work.

Any words will be cordially accepted. Thanks in advance.

Edit: Here is my query that I have used so far

SELECT DISTINCT SERVER.*, category.category_name, IF(EXISTS(SELECT DISTINCT id FROM server_hit WHERE server_hit.server_id = SERVER.server_id), (SELECT SUM(hit_count) FROM server_hit WHERE server_hit.server_id = server.server_id GROUP BY server_id), 0) AS 'total_hit_count' FROM server, category, server_hit WHERE server.category_id = category.category_id GROUP BY server.server_id;

Answer

If I’m understanding correctly, I believe the following should work.

The key is to issue the LEFT JOIN so that even a server with no matching record in the server_hit table will still show in the final output, but with a 0 sum.

SELECT s.server_id, s.server_name, s.server_url, s.category_id, c.category_name, IFNULL(SUM(sh.hit_count), 0)
FROM server s
INNER JOIN category c ON s.category_id = c.category_id
LEFT JOIN server_hit sh ON s.server_id = sh.server_id
GROUP BY s.server_id, s.server_name, s.server_url, s.category_id, c.category_name

Add IF EXISTS to handle NULL issue

SELECT DISTINCT s.server_id, s.server_name, s.server_url, s.category_id, c.category_name, IF(EXISTS(SELECT id FROM server_hit WHERE sh.server_id = s.server_id), SUM(sh.hit_count), 0) as 'total_hit_count' 
FROM server s 
INNER JOIN category c ON s.category_id = c.category_id 
LEFT JOIN server_hit sh ON s.server_id = sh.server_id GROUP BY s.server_id, s.server_name, s.server_url, s.category_id, c.category_name