#17 ✓resolved
Georg Ledermann

BUG: DISTINCT is used for SUM

Reported by Georg Ledermann | October 24th, 2008 @ 06:26 AM

There is something wrong with the automatic DISTINCT. Please have a look at the following example:

Order.build_search.sum(:amount)

This generates the following SQL:

SELECT sum(DISTINCT `orders`.amount) AS sum_amount
FROM `orders`

The DISTINCT is not correct here.

Testet with - Searchgasm 1.5.3 released 2008-10-21 - Rails 2.1.2 (released yesterday)

Comments and changes to this ticket

  • Ben Johnson

    Ben Johnson October 24th, 2008 @ 11:53 AM

    • State changed from “new” to “open”

    I added a condition to only enforce this if joins are present. The problem is when you start joining tables, you will get duplicate orders which will return a false sum. Also ActiveRecord created the :distinct option for calculations, so I would assume they would handle it properly.

    What do you think?

  • Georg Ledermann

    Georg Ledermann October 25th, 2008 @ 08:13 AM

    IMHO ActiveRecord uses DISTINCT only for COUNT (if :include is given), not for other calucations like SUM etc. So using DISTINCT for SUM/MAX/MIN etc. should be avoided in every cases.

    See this example using joins:

    LineItem.build_search(:conditions => { :order => { :user => { :name_starts_with => 'A' }}}).sum(:amount)
    

    (Sum up the amount of all line items which belong to User which name is starting with 'A')

    Searchgasm builds "SUM(DISTINTC amount) FROM ..." I think this is not correct. I think summing up only distinct values is always wrong.

    BTW, if you use the following syntax, no DISTINCT is generated:

    LineItem.scoped(:conditions => { :order => { :user => { :name_starts_with => 'A' }}}).sum(:amount)
    

    I think the automatic DISTINCT should be used for COUNT only.

  • Ben Johnson

    Ben Johnson October 25th, 2008 @ 11:35 AM

    • State changed from “open” to “resolved”

    Done, the automatic distinct is only used on count and when their are joins. Thanks for letting me know about this.

  • MatthewDJarvis

    MatthewDJarvis August 2nd, 2019 @ 09:03 AM

    This example is very good to solve this issue and I hope users easily understand from this about the issue they are facing. From uk assignments I get many important examples which help me in coding.

  • rosstaylor505

    rosstaylor505 January 9th, 2020 @ 06:48 AM

    I would like to thank you for the efforts you have made in writing this article. I am hoping the same best work from you in the future as well. In fact your creative writing abilities has inspired me to start my own BlogEngine blog now. Really the blogging is spreading its wings rapidly. Your write up is a fine example of it.
    What is the purpose of wearing socks?

Please Sign in or create a free account to add a new ticket.

With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.

New-ticket Create new ticket

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile ยป

Provides common named scopes and object based searching.

People watching this ticket

Pages