CODEWITHSUNDEEP
sqlsybase
Greg
Greg

Reputation: 140

SQL string aggregation based on a group by

I have a dataset of INT, varchar. The ints can be grouped into small sets of 1 to 5, and I want to see the count of all the corresponding texts. E.g. input table (apols for format)

 1,a
 2,a
 2,b
 3,a
 3,q 
 3,z
 4,a

I can group by the Int, but have no idea how I can concat across rows. In the above i would want to know counts of:

a,2
ab,1
aqz,1

Can I do this purely in SQL, ideally in a generic db agnostic way?

Upvotes: 2

Views: 4231

Answers (3)

Nick
Nick

Reputation: 147216

If you're using MySQL, you can use GROUP_CONCAT to concatenate the characters into strings for each integer value, then COUNT the occurrences of each string:

SELECT str, COUNT(*) AS count
FROM (SELECT GROUP_CONCAT(str ORDER BY str SEPARATOR '') AS str
      FROM data
      GROUP BY i) d
GROUP BY str 
ORDER BY str

Demo on SQLFiddle

In Oracle you can use LISTAGG:

SELECT str, COUNT(*) AS count
FROM (SELECT LISTAGG(str, '') WITHIN GROUP (ORDER BY str) AS str
      FROM data
      GROUP BY i) d
GROUP BY str 
ORDER BY str

Demo on SQLFiddle

In SQL Server 2017 and above you can use STRING_AGG:

SELECT str, COUNT(*) AS count
FROM (SELECT STRING_AGG(str, '') WITHIN GROUP (ORDER BY str) AS str
      FROM data
      GROUP BY i) d
GROUP BY str 
ORDER BY str

Demo on SQLFIddle

In PostgreSQL you can also use STRING_AGG:

SELECT str, COUNT(*) AS count
FROM (SELECT STRING_AGG(str, '' ORDER BY str) AS str
      FROM data
      GROUP BY i) d
GROUP BY str 
ORDER BY str

Demo on SQLFiddle

In all cases the output is:

STR     COUNT
a       2
ab      1
aqz     1

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270773

Sybase supports the LIST() function. So:

select chrs, count(*) as cnt
from (select t.number, list(t.chr, '' order by t.chr) as chrs
      from t
      group by t.number
     ) n
group by chrs
order by count(*) desc;

Upvotes: 1

zealous
zealous

Reputation: 7503

You can do by using string_agg() in SQL Server. check demo here sqlfiddle

select 
  cnct_str, 
  count(*) as count
from
(
    select 
        string_agg(chars, '') within group (order by chars) as cnct_str
    from tabs
    group by id
) val
group by cnct_str

Upvotes: 0

Related Questions