I get this error after execute my query
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) as totalcontents from contents as c inner join categories as cat ON cat.id = ' at line 1
My sql like this
select z.name as zone_name, COUNT(c.*) as totalcontents
from contents as c
inner join categories as cat
ON cat.id = c.category_id
inner join zones as z
ON z.id = cat.zone_id where c.created_by = 14 group by z.id
When you are using INNER JOIN then COUNT(*) is ok to use.
select z.name as zone_name, COUNT(*) as totalcontents ....
OR
select z.name as zone_name, COUNT(z.name) as totalcontents ....
When you use group by function to specify column to display column other column to display given error. I think you try this
select z.name as zone_name, COUNT(z.name) as totalcontents
from contents as c
inner join categories as cat
ON cat.id = c.category_id
inner join zones as z
ON z.id = cat.zone_id where c.created_by = 14 group by z.name
or
select z.name as zone_name,totalcontents
from zones as z
join (select z.id as id, COUNT(z.id) as totalcontents
from contents as c
inner join categories as cat
ON cat.id = c.category_id
inner join zones as z
ON z.id = cat.zone_id where c.created_by = 14 group by z.id) a
ON z.id = a.id