CODEWITHSUNDEEP
mysqlsqlsubqueryleft-join
Alan John
Alan John

Reputation: 51

Select count of different values in mysql

I have two tables, A and B, structured as follows. 

CREATE TABLE TableA (
  `id` INTEGER,
  `code` VARCHAR(1),
  `type` VARCHAR(1)
);

INSERT INTO TableA
  (`id`, `code`, `type`)
VALUES
  ('1', 'A', 'r'),
  ('2', 'A', 'o'),
  ('3', 'A', 'u'),
  ('4', 'B', 'h');

CREATE TABLE TableB (
  `id` INTEGER,
  `sid` INTEGER,
  `code` VARCHAR(1)
);

INSERT INTO TableB
  (`id`, `sid`, `code`)
VALUES
  ('1', '1', 'A'),
  ('1', '1', 'A'),
  ('1', '1', 'A'),
  ('2', '1', 'B'),
  ('2', '1', 'B'),
  ('1', '2', 'A'),
  ('1', '2', 'A'),
  ('2', '2', 'A');

the output of this query should be like this

sid      r     o     u
1        3     2     0
2        2     1     0

table A has a foreign key from table B which is the id. what I want to get is how many times each sid accessed each type of table A.

Upvotes: 0

Views: 49

Answers (1)

forpas
forpas

Reputation: 164089

Left join Table B to Table A and aggregate:

select b.sid,
  sum(a.type = 'r') r,
  sum(a.type = 'o') o,
  sum(a.type = 'u') u
from TableB b left join TableA a
on b.id = a.id
group by b.sid

See the demo.
Results:

| sid | r   | o   | u   |
| --- | --- | --- | --- |
| 1   | 3   | 2   | 0   |
| 2   | 2   | 1   | 0   |

Upvotes: 3

Related Questions