How to use mySQL count using joins?
I am having following database schema, I want to fetch name of all categories with no of quotes related to that category . The query that i wrote givi开发者_运维百科ng me one row only can u please tell me the resource efficient query.
SELECT SC.Name, Count(*) AS Quotes
FROM status_categories AS SC
INNER JOIN status_quotes AS SQ ON SC._id = SQ._category_id
GROUP BY SC.Name
SELECT status_categories.NAME, COUNT(status_quotes.category_id)
FROM status_categories JOIN status_quotes ON status_categories._id = status_quotes.category_id
GROUP BY status_categories._id;
Try the following:
SELECT `c`.`name`, COUNT(*) AS `Number of quotes`
FROM `status_categories` AS `c`
INNER JOIN `status_quotes` AS `q`
ON `q`.`category_id` = `c`.`_id`
GROUP BY `c`.`_id`;
EDIT
Feel free to leave out the ` character. But that is the safe way of doing it, even though it looks a bit nasty.
精彩评论