开发者

How to show rows with zero counts in mySQL group by query?

I tried looking around for similar solutions but it didn't seem to help much in my case. I have a table as such:

locations => |longname|name|locations|

topics    =>开发者_JS百科; |longname|name|tag|

and I am trying to get the count of longnames with a specific atag for a particular location. The query I have so far also tries to force reordering, which is why you see the location IN and FIELD. However, if there aren't any people in a location for a particular tag, it omits it entirely from the response. I'd like it to return 0. How can I do that?

select count(b.longname) as count, a.location 
 from `topics` b, locations a 
  where tag = 'atag' and a.longname = b.longname 
  and location IN ('Japan', 'France', 'Italy') 
  group by location 
  order by FIELD(location, 'Japan', 'France', 'Italy')


Perhaps this? :

SELECT COUNT(t.longname) as cnt, l.location 
FROM locations l 
  LEFT JOIN topics t
    ON  t.longname = l.longname
    AND t.tag = 'atag'      
WHERE l.location IN ('Japan', 'France', 'Italy') 
GROUP BY l.location 
ORDER BY FIELD(l.location, 'Japan', 'France', 'Italy')
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜