CODEWITHSUNDEEP
ruby-on-railsruby
user2950593
user2950593

Reputation: 9627

rewrite sql statement with max and groupby in ruby

I have this my sql view:

SELECT
    `reports`.`date` AS `date`,
    `reports`.`book_title` AS `book_title`,
    max(
        `reports`.`royalty_type`
    ) AS `royalty_type`,
    max(
        `reports`.`avg_list_price`
    ) AS `avg_list_price`
FROM
    `reports`
GROUP BY
    `reports`.`date`,
    `reports`.`book_title`,
    `reports`.`marketplace`

As far as I understand it groups results by date, then, by book_title and then by market place and then it selects max royalty_type and avg_list_price within this small subgroups

How do I rewrite this in rails activerecord? I don't know how to select max within this small groups in activerecord.

Upvotes: 0

Views: 25

Answers (1)

Ursus
Ursus

Reputation: 30056

Try this one

Report.group(:date, :book_title, :marketplace).select('date, book_title, MAX(royalty_type) AS royalty_type, MAX(avg_list_price) AS avg_list_price')

Upvotes: 2

Related Questions