How to make select with variable using Google Sheets

Question

I have a Google sheet with several columns, where are recorded support requests from clients.

     A                B                      C
-+---------------+------------------------+-------------
1| Date-1        | John                   | Ticket-101 
2| Date-1        | Anita                  | Ticket-102
3| Date-2        | John                   | Ticket-103
4| Date-3        | Dani                   | Ticket-104
5| Date-3        | Billy                  | Ticket-105

I want to create two new columns with statistical data about the clients. In these new columns, I want to have the client name and number of opened support tickets.

The end result must be:

     A                B             C            D          E
-+---------------+------------+-------------+-----------+---------------
1| Date-1        | John       | Ticket-101  | John      |   2    |
2| Date-1        | Anita      | Ticket-102  | Anita     |   1    |
3| Date-2        | John       | Ticket-103  | Dani      |   1    |
4| Date-3        | Dani       | Ticket-104  | Billy     |   1    |
5| Date-3        | Billy      | Ticket-105  | 

I created the D column in this way:

=UNIQUE(QUERY(B1:B))

For counting how many times the client contact us I use:

=COUNTA(IFERROR(QUERY(B1:B, "select B where B='John'", 0)))

Of course, this is a very stupid solution, because for every new client I must to create a new formula with

....where B='Client name'".....

I'm wondering is it possible to create a formula in a way in which the name of the client is automatically populated? I imagine something like that:

=ARRAYFORMULA(COUNTA(IFERROR(QUERY(B1:B, "select B where B='value-of-D'", 0))))

Answer 1

=QUERY(B:C, 
 "select B,count(C) 
  where B!='' 
  group by B 
  label count(C)''", 0)

also you can order it like:

=QUERY(B:C, 
 "select B,count(C) 
  where B!='' 
  group by B 
  order by count(C) desc
  label count(C)''", 0)