How to group a group and count the number of elements in this group sql-CodePudding

I am a beginner in sql and I am wondering how can I get the number of a group in a group.

Here I would like to group the table again according to the column "COL1" and then count the number of elements. This should output at the end that for the group B two is counted and for all others one.

For this first I created a table

CREATE TABLE tablename (`ID` INTEGER, `Col1` VARCHAR(1), `Col2` VARCHAR(1), `Col3` VARCHAR(6));

INSERT INTO tablename (`ID`, `Col1`, `Col2`, `Col3`) VALUES
  ('1', 'A', 'Q', 'green'),
  ('2', 'B', 'R', 'blue'),
  ('3', 'B', 'S', 'red'),
  ('4', 'C', 'T', 'purple'),
  ('5', 'D', 'U', 'orange'),
  ('6', 'E', 'R', 'black'),
  ('7', 'F', 'U', 'brown'),
  ('8', 'F', 'V', 'pink'),
  ('9', 'G', 'W', 'white'),
    ('10', 'B', 'R', 'blue');

and then I grouped the columns

SELECT count(*),Col1,GROUP_CONCAT(Col2) Col2,
       GROUP_CONCAT(Col3) Col3
FROM tablename a
GROUP BY Col1, Col2

CodePudding user response：

I hope I understood correctly what you want

select sum(sm),Col1,GROUP_CONCAT(Col2),GROUP_CONCAT(Col3) from (
    SELECT count(*) as sm,Col1,GROUP_CONCAT(Col2) Col2,GROUP_CONCAT(Col3) Col3
    FROM tablename a GROUP BY Col1, Col2
) b GROUP BY Col1