CODEWITHSUNDEEP
sqlsqlite
user2351
user2351

Reputation: 1

How to tally distinct combinations of rows in sqlite?

Say I have a collection of glass jars with some plastic balls of different colors in them. There can be at most one ball of each color in every jar and every jar contains one or more balls.

I represent this collection with an sqlite table with the following columns:

JarID INTEGER, Color TEXT

A collection of three jars might then look like this:

JarID Color    
0,  'Red'    
1,  'Red'    
1,  'Green'    
2,  'Red'    
2,  'Blue'    
3,  'Red'

I'd like to write a query that will find all the different color combinations that exist in my jar collection, and list each combination alongside the total number of jars with that combination. For the table above, the query should return either:

'Red', 2    
'Red,Green', 1    
'Red,Blue', 1

Or:

Red
2
Red
Green    
1    
Red    
Blue    
1

Currently I have a terrible mess of common table expressions and window functions that seems to achieve the desired result, but I can't help feeling that I'm missing some elegant, standard SQL solution to this.

Upvotes: -1

Views: 99

Answers (1)

Chris Maurer
Chris Maurer

Reputation: 2539

The Group_Concat function is what you're looking for, but first you need to aggregate and order your list so red-green-red comes out the same as green-red-red.

Do that with:

Select Jar_Id, Color
From Jars
Group By Jar_Id, Color
Order by Jar_Id, Color

Now feed that to a group_concat subquery to make a list of colors by jar_id:

Select Jar_Id, Group_Concat(Color) as Combos
From <first subquery>
Group By Jar_Id

Then feed that to an aggregator to count occurrences, and you're done:

Select Combos, count(*) as Occurrences
From <second subquery>
Group By Combos
Order by Combos

Put it all together with:

Select Combos,count(*) as Occurrences
From (
    Select Jar_Id, Group_Concat(Color) as Combos
    From (
        Select Jar_Id, Color
        From Jars
        Group By Jar_Id, Color
        Order by Jar_Id, Color
    )
    Group By Jar_Id
)
Group By Combos
Order by Combos

One caution: Order Bys in a subquery are not strictly defined in SQL and might be ignored, but most implementations will do them.

Upvotes: 1

Related Questions