Select count of different values in mysql

Question

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.

The code column appearing in both tables would seem to be unnormalized data, and it seriously throws off anyone looking at your data. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jan 16, 2020 at 17:45

forpas · Accepted Answer · 2020-01-16 17:43:47Z

3

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   |

answered Jan 16, 2020 at 17:43

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alan John Over a year ago

Thank you so much for your help, but what if I have more than 50 types in table A. is it useful to repeat the sum function for 50 times ?

forpas Over a year ago

MySql does not offer any pivot functionality, so in this case the only way is conditional aggregation for each type like the code in my answer.

Collectives™ on Stack Overflow

Select count of different values in mysql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related