MYSQL - how to select with group by and having

I have this schema:

table = users
user_id - integer
user_name - string

table = transaction
transaction_id - string
user_id - integer

this sample data:

user_id -  user_name
2       -  Jhon
3.      -  barry

transaction_id - user_id
19123          -  2
20123          -  2
20124          -  2
21123          -  2

I need to get how many transactions the user jhon did in 19 and 20 only the first 2 digits from the transaction_id is the year, but I can't seem to group them, what I have is:

select u.user_name
from transaction t join users u on u.user_id = t.user_id
group by (substr(t.transaction_id, 1, 2))
where <I have no idea in how to fill this>

what I want as a result is:

jhon 1 2

1 transction in 19 2 transactions in 20

Solution

It would be better to save dates in mysql way 2022-12-31, so you cqan use date function withput converting.

You need to GROUP By Nam,ame and the first 2 digits

And the WHERE clause belongs always before the group by

CREATE TABLE users (
  `user_id` INTEGER,
  `user_name` VARCHAR(5)
);

INSERT INTO users
  (`user_id`, `user_name`)
VALUES
  ('2', 'Jhon'),
  ('3.', 'barry');

CREATE TABLE transaction (
  `transaction_id` INTEGER,
  `user_id` INTEGER
);

INSERT INTO transaction
  (`transaction_id`, `user_id`)
VALUES
  ('19123', '2'),
  ('20123', '2'),
  ('20124', '2'),
  ('21123', '2');

select u.user_name, Count(*)
from transaction t join users u on u.user_id = t.user_id
where u.user_name = 'Jhon' AND (substr(t.transaction_id, 1, 2)) IN  (19,20)
group by u.user_name,(substr(t.transaction_id, 1, 2))

user_name | Count(*)
:-------- | -------:
Jhon      |        1
Jhon      |        2

select u.user_name
, SUM((substr(t.transaction_id, 1, 2)) = 19) As `Count_19`
, SUM((substr(t.transaction_id, 1, 2)) = 20) As `Count_20`
from transaction t join users u on u.user_id = t.user_id
where u.user_name = 'Jhon' AND (substr(t.transaction_id, 1, 2)) IN  (19,20)
group by u.user_name

user_name | Count_19 | Count_20
:-------- | -------: | -------:
Jhon      |        1 |        2

db<>fiddle here