Box Posted March 2, 2010 Share Posted March 2, 2010 Not really sure on how to do this as i'm not too great with mysql. I have 2 tables categories and news. news has a unique id and a category id categories has the catagory id and the name to be used on it. Im trying to make some stats that lists all the category names and the number of times it has been used in the news table This is my latest attempt at it. SELECT COUNT( * ) AS rows, news.category, categories.catname FROM news, categories GROUP BY category ORDER BY category WHERE catid=category Link to comment https://forums.phpfreaks.com/topic/193941-using-a-count-when-using-2-tables/ Share on other sites More sharing options...
Dennis1986 Posted March 2, 2010 Share Posted March 2, 2010 SELECT c.*, COUNT(n.category) AS newsCount FROM categories AS c LEFT JOIN news AS n ON (n.category = c.id) ORDER BY c.catname ASC Something like that. It's a bit confusing to create it for you since I don't know the fields and they seem quite similar to eachother Link to comment https://forums.phpfreaks.com/topic/193941-using-a-count-when-using-2-tables/#findComment-1020636 Share on other sites More sharing options...
Box Posted March 2, 2010 Author Share Posted March 2, 2010 fantastic! I've managed to get it right thanks to your code which wasnt too far off what I needed. just had to add in the group by and change the id to catid the working code being: SELECT c.*, COUNT(n.category) AS newsCount FROM categories AS c LEFT JOIN news AS n ON (n.category = c.catid) GROUP BY c.catname ORDER BY c.catname ASC Link to comment https://forums.phpfreaks.com/topic/193941-using-a-count-when-using-2-tables/#findComment-1020645 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.