SQL Count Distinct Colums and Return all rows

Question

I have table in MySQL and I am trying to filter the data from ColA and count the instances of a result in ColB while still getting all matching rows

TABLE Name: CityCityStuff

ColA	ColB	ColC
CityA	CityB	Other Stuff
CityA	CityC	Other Stuff
CityB	CityC	Other Stuff
CityB	CityA	Other Stuff
CityB	CityA	Other Stuff
CityC	CityA	Other Stuff
CityC	CityA	Other Stuff
CityC	CityB	Other Stuff
CityC	CityD	Other Stuff
CityC	CityE	Other Stuff
CityC	CityE	Other Stuff

I want to know:

SELECT * FROM CityCityStuff WHERE ColA = 'CityC'

And then how many times each ColB is repeated while still getting all rows where ColA = CityC

Results returned look like:

ColA	ColB	ColC	TotalEach
CityC	CityA	Other Stuff	2
CityC	CityA	Other Stuff	2
CityC	CityB	Other Stuff	1
CityC	CityD	Other Stuff	1
CityC	CityE	Other Stuff	2
CityC	CityE	Other Stuff	2

OR:

ColA	ColB	ColC	TotalEach
CityC	CityA	Other Stuff	2
CityC	CityA	Other Stuff
CityC	CityB	Other Stuff	1
CityC	CityD	Other Stuff	1
CityC	CityE	Other Stuff	2
CityC	CityE	Other Stuff

Answer 1

Here is my attempt db-fiddle

Tested in MySql v5.5, 5.6, 5.7, 8.0

CREATE TABLE CityCityStuff 
(
    ColA    VARCHAR(512),
    ColB    VARCHAR(512),
    ColC    VARCHAR(512)

);

INSERT INTO CityCityStuff (ColA , ColB , ColC ) VALUES
    ('CityA ', 'CityB ', 'Other Stuff'),
    ('CityA ', 'CityC ', 'Other Stuff'),
    ('CityB ', 'CityC ', 'Other Stuff'),
    ('CityB ', 'CityA ', 'Other Stuff'),
    ('CityB ', 'CityA ', 'Other Stuff'),
    ('CityC ', 'CityA ', 'Other Stuff'),
    ('CityC ', 'CityA ', 'Other Stuff'),
    ('CityC ', 'CityB ', 'Other Stuff'),
    ('CityC ', 'CityD ', 'Other Stuff'),
    ('CityC ', 'CityE ', 'Other Stuff'),
    ('CityC ', 'CityE ', 'Other Stuff')

Select * from CityCityStuff;

Select cola, colb, colc, (Select count(*) as TotalEach from CityCityStuff a Where a.ColB=b.ColB and cola='cityC') as TotalEach
from CityCityStuff b
where cola='cityC'

colA	colB	colC	TotalEach
CityC	CityA	Other Stuff	2
CityC	CityA	Other Stuff	2
CityC	CityB	Other Stuff	1
CityC	CityD	Other Stuff	1
CityC	CityE	Other Stuff	2
CityC	CityE	Other Stuff	2

Answer 2

You can use this count() windows function in MariaDB (and MySQL 8 and other DBMS). The partition by colA, colB essentially groups your data when counting.

select 
  colA, 
  colB, 
  colC, 
  count(*) over (partition by colA, colB) as totalEach
from citycitystuff
where colA = 'CityC'

colA	colB	colC	totalEach
CityC	CityA	Other Stuff	2
CityC	CityA	Other Stuff	2
CityC	CityB	Other Stuff	1
CityC	CityD	Other Stuff	1
CityC	CityE	Other Stuff	2
CityC	CityE	Other Stuff	2

fiddle found here