Railsana Railsana - 2 months ago 16
SQL Question

Rails: Sum only once per particular column combination

I have this kind of table:

dated category_id policy_id available
2016-01-01 1 1 5
2016-01-01 1 2 5
2016-01-01 2 1 2
2016-01-02 1 1 4
2016-01-03 1 1 3
2016-01-03 2 1 3
2016-01-03 2 2 3


I want to show the availability (each category added) for each date, independent of the policy. The results in this case should be:

2016-01-01 => 7
2016-01-02 => 4
2016-01-03 => 6


I am using the groupdate gem which allows queries like this:

Item.group_by_day(:dated).sum(:available)


This query of course returns 12 (should be 7) for the first date and 9 (should be 6) for the last.
How can I use only one date/category-combination? I tried
.uniq(:category_id)
, but that doesn't seem to work.

Thanks for any help!

Answer

I'd suggest this database subquery…

subquery = Item.select('DISTINCT ON(items.dated, items.category_id) items.id').order(:dated, :category_id).to_sql

…and then using Rails group_by like so

  available_by_date = items
      .where("items.id in (#{subquery})")
      .group_by_day(:dated)
      .sum(:available)
Comments