0

I use PostgreSQL. Given sets of groups such as:

CREATE TABLE IF NOT EXISTS t (
   id INTEGER  NOT NULL,
   value TEXT NOT NULL
);

INSERT INTO t (id, value) VALUES
(1, 'A'),
(1, 'B'),
(2, 'B'),
(2, 'A'),
(3, 'A'),
(4, 'A'),
(4, 'B'),
(4, 'C');

I wish to count rows per set and achieve the following result:

{'A', 'B'}: 2
{'A'}: 1
{'A', 'B', 'C'}: 1

2 Answers 2

0

You can use string_agg() to aggregates strings. But your desired result shows arrays. Use the simpler array_agg() for that.

Either way, you need to aggregate twice for your desired result:

SELECT values, count(*) AS ct
FROM  (
   SELECT id, array_agg(value ORDER BY value) AS values
   FROM   t
   GROUP  BY id
   ) sub
GROUP  BY values
ORDER  BY ct;

Typically, it's faster to use ORDER BY once in a subquery, instead of adding it to the aggregate function (which results in one sort per group):

SELECT values, count(*) AS ct
FROM  (
   SELECT id, array_agg(value) AS values
   FROM  (SELECT id, value FROM t ORDER BY 1, 2) sub
   GROUP  BY 1
   ) sub
GROUP  BY 1
ORDER  BY 2;

db<>fiddle here

See:

0

I found the function group_concat for MySQL or string_agg for PostgreSQL:

http://sqlfiddle.com/#!9/42f994/3

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.