Query to select distinct values of a column and count them, but also detail them by another column-CodePudding

Basiclly how to turn this

Type	Subtype	Notes
A	S1	string1
A	S2	string1
A	S2	string1
A	S2	string1
A	S3	string1
A	S3	string1

into this

Type	Notes	Details
A	string1	S1 (1), S2 (3), S3(2)

is it even possible?

In oracle, I've been going about it with SELECT DISTINCTs and GROUP BYs, some JOINS but I'm not really getting what I want

CodePudding user response：

Aggregate twice:

SELECT type,
       notes,
       LISTAGG(subtype || ' (' || num_subtypes || ')', ', ')
         WITHIN GROUP (ORDER BY subtype) AS details
FROM   (
  SELECT type,
         notes,
         subtype,
         COUNT(*) AS num_subtypes
  FROM   table_name
  GROUP BY type, notes, subtype
)
GROUP BY type, notes

Which, for the sample data:

CREATE TABLE table_name (Type, Subtype, Notes) AS
SELECT 'A', 'S1', 'string1' FROM DUAL UNION ALL
SELECT 'A', 'S2', 'string1' FROM DUAL UNION ALL
SELECT 'A', 'S2', 'string1' FROM DUAL UNION ALL
SELECT 'A', 'S2', 'string1' FROM DUAL UNION ALL
SELECT 'A', 'S3', 'string1' FROM DUAL UNION ALL
SELECT 'A', 'S3', 'string1' FROM DUAL;

Outputs:

TYPE	NOTES	DETAILS
A	string1	S1 (1), S2 (3), S3 (2)

fiddle