can not get correct results with group by in mysql

advertisements

I have 2 SQL tables

CREATE TABLE A(
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  name CHAR(1) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE B(
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  A_id INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (id)
);

INSERT INTO A VALUES (1, 'A'), (2, 'B'), (3, 'C'), (4, 'A');
INSERT INTO B VALUES (1, 1), (2, 2), (3, 4), (4, 4);

The tables look this way:

select * from A;
+----+------+
| id | name |
+----+------+
|  1 | A    |
|  2 | B    |
|  3 | C    |
|  4 | A    |
+----+------+

select * from B;
+----+------+
| id | A_id |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    4 |
|  4 |    4 |
+----+------+

Now I want to find out how many each of the elements from table A are there in table B. Using other words I want to see:

A = 3
B = 1
C = 0

I tried to do this with: SELECT name, count(*) FROM A, B WHERE A.id = A_id GROUP BY A.id;, but it returns something completely weird. Can someone help me?


Query

SELECT a.name,COUNT(b.A_id) as `count`
FROM A a
LEFT JOIN B b
ON a.id=b.A_id
GROUP BY a.name;

Fiddle Demo