Category table:
id, name
---------
1 cat1
2 cat2
3 cat3
4 cat4
Article table:
id, cid, title
--------------
1 1 title1
2 3 title2
3 1 title3
4 2 title4
How to select all categories and count number of each category articles in MySQL? I need a mysql query that will select all category and total count of articles belonging to each category and order by category id.
Expected output table:
+-------+-------+
| name | count |
+-------+-------+
| cat1 | 2 |
| cat2 | 1 |
| cat3 | 1 |
| cat4 | 0 |
+-------+-------+
You can also use subquery like
SELECT c.name,(select count(*) from article where cid = c.id) as count FROM cat c WHERE 1 GROUP BY c.id
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments