How can I count the count-values of Sequel::Dataset#group_and_count

Question

I have a table and I count how often an element occurs. For this I can use Sequel::Dataset#group_and_count.

But now I want to count this result. How can I do this?

Example

require 'sequel'
Sequel.extension :pretty_table
DB = Sequel.sqlite()

DB.create_table( :test ){
  add_field :key,    :type => :nvarchar, :size => 10
  add_field :value, :type => :nvarchar, :size => 10
}

'a'.upto('f'){|a|
  a.upto('x'){|b|
    DB[:test].insert( :key => a, :value => b)
  }
}

sel = DB[:test].group_and_count(:key)
puts  Sequel::PrettyTable.string(sel)

This gives me:

+-----+---+
|count|key|
+-----+---+
|   24|a  |
|   23|b  |
|   22|c  |
|   21|d  |
|   20|e  |
|   19|f  |
+-----+---+

Now I want to count, how often a count-value occurs.

My result should be:

+-----------+-----+
|count_value|count|
+-----------+-----+
|   24      |  1  |
|   23      |  1  |
|   22      |  1  |
|   21      |  1  |
|   20      |  1  |
|   19      |  1  |
+-----------+-----+

I can get this result with:

mycount = Hash.new(0)
DB[:test].group_and_count(:key).each{| res |
  mycount[res[:count]] += 1
}
p mycount # {24=>1, 23=>1, 22=>1, 21=>1, 20=>1, 19=>1}

With SQL (see SQL Group by Count of Counts) I can execute the following command:

puts DB.fetch(<<-sql ).all
  select count_value, count(*) as count from (
    select count(*) as count_value from test group by key
  )
  group by count_value
sql

But I'm looking for a way to do this with a Sequel command (if possible).

What I tried:

group_and_count is not chainable:

#SQLite3::SQLException: no such column: count (Sequel::DatabaseError)
sel = DB[:test].group_and_count(:key).group_and_count(:count)

It is also not possible to rename the :count

sel = DB[:test].group_and_count(:key).select(:count.as(:count_value))

If I define a view, I get a DatabaseError:

DB.create_view(:count_testkey,  DB[:test].group_and_count(:key))
sel = DB[:count_testkey].group_and_count(:count)

#SQLite3::SQLException: aggregate functions are not allowed in the GROUP BY clause (Sequel::DatabaseError)
puts  Sequel::PrettyTable.string(sel)

knut · Accepted Answer · 2014-01-23T16:19:53.343

I found a solution:

sel = DB[:test].select(:count_value, Sequel::SQL::Function.new(:count, Sequel::Dataset::WILDCARD).as(:count)).group_by(:count_value)
.from( 
    DB[:test].select(Sequel::SQL::Function.new(:count, Sequel::Dataset::WILDCARD).as(:count_value)
        ).group_by(:key)
  )

puts sel.sql.downcase
puts  Sequel::PrettyTable.string(sel)

This results in (SQL-command is 'pretty-printed by me):

select `count_value`, count(*) as 'count' from (
  select count(*) as 'count_value' from `test` group by `key`) as 't1' 
group by `count_value`
+-----+-----------+
|count|count_value|
+-----+-----------+
|    1|         19|
|    1|         20|
|    1|         21|
|    1|         22|
|    1|         23|
|    1|         24|
+-----+-----------+

Based on this solution I defined a new method Sequel::Dataset#group_and_countcount.

Here a complete example:

require 'sequel'
Sequel.extension :pretty_table
DB = Sequel.sqlite()

DB.create_table( :test ){
  add_field :key,    :type => :nvarchar, :size => 10
  add_field :value, :type => :nvarchar, :size => 10
}

'a'.upto('f'){|a|
    a.upto('x'){|b|
      DB[:test].insert( :key => a, :value => b)
      DB[:test].insert( :key => 'x', :value => b) if a == 'a' #force one double count
    }
}

puts "Test DB[:test].group_and_count(:key)"
sel = DB[:test].group_and_count(:key)
puts  Sequel::PrettyTable.string(sel) 

module Sequel
  class Dataset
    def group_and_countcount(key)
      self.unfiltered.select(:count_value, Sequel::SQL::Function.new(:count, Sequel::Dataset::WILDCARD).as(:count)).group_by(:count_value)
        .from( 
            self.select(Sequel::SQL::Function.new(:count, Sequel::Dataset::WILDCARD).as(:count_value)
                ).group_by(key)
          )
    end #group_and_countcount(key)
  end
end

puts "Test DB[:test].group_and_countcount(:key)"
sel = DB[:test].group_and_countcount(:key)
puts  Sequel::PrettyTable.string(sel)

The result:

Test DB[:test].group_and_count(:key)
+-----+---+
|count|key|
+-----+---+
|   24|a  |
|   23|b  |
|   22|c  |
|   21|d  |
|   20|e  |
|   19|f  |
|   24|x  |
+-----+---+
Test DB[:test].group_and_countcount(:key)
+-----+-----------+
|count|count_value|
+-----+-----------+
|    1|         19|
|    1|         20|
|    1|         21|
|    1|         22|
|    1|         23|
|    2|         24|
+-----+-----------+

How can I count the count-values of Sequel::Dataset#group_and_count

Example

What I tried:

1 Answers1