Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL Count Distinct Colums and Return all rows

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
Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search