Re: must appear in the GROUP BY clause or be used in an aggregate function problem

On Tue, Jan 31, 2012 at 1:43 PM, Edward W. Rouse <erouse(at)comsquared(dot)com>wrote:

> NM, I figured it out. The mere presence of an aggregate function and/or
> the group by clause is what’s causing all the hate for me. I will take a
> whack at getting this to work without them. Thanks all.
>

All columns that are not in an aggregate function MUST be in the group by
clause if there is any column in an aggregate function. If you can get rid
of all aggregation, then you won't have to have the group by, either. You
could use a correlated subquery to get the most recent report date, rather
than using max. That would allow you to ditch all of the other
aggregation, I suspect. But unless there is any chance of different
versions of the name fields for a given id, then it is harmless to drop the
MAX() function call and add them to the group by clause. You get the same
effect.

> ****
>
> ** **
>
> *From:* pgsql-sql-owner(at)postgresql(dot)org [mailto:
> pgsql-sql-owner(at)postgresql(dot)org] *On Behalf Of *Edward W. Rouse
> *Sent:* Tuesday, January 31, 2012 4:27 PM
> *To:* 'Samuel Gendler'
>
> *Cc:* pgsql-sql(at)postgresql(dot)org
> *Subject:* Re: [SQL] must appear in the GROUP BY clause or be used in an
> aggregate function problem****
>
> ** **
>
> I would love to remove most of the aggregate functions. I am trying to
> update an existing query to provide better data and started with the
> existing one (which is where the MAX for the names came from). I originally
> tried to remove those, but I get the group by/aggregate function error if I
> do. I guess I don’t understand enough to know why these are being forced
> into that category. I have written queries in the past that had several
> columns that were not considered requiring aggregation. Can someone
> enlighten me on why these are being marked as requiring group/aggregation.
> ****
>
> ** **
>
> Something with the way the table are joined perhaps? Can I remove the
> joins and put the a.id = c.id in the where clause and get rid of this?****
>
> ** **
>
> *From:* pgsql-sql-owner(at)postgresql(dot)org [mailto:
> pgsql-sql-owner(at)postgresql(dot)org] *On Behalf Of *Samuel Gendler
> *Sent:* Tuesday, January 31, 2012 3:57 PM
> *To:* Edward W. Rouse
> *Cc:* pgsql-sql(at)postgresql(dot)org
> *Subject:* Re: [SQL] must appear in the GROUP BY clause or be used in an
> aggregate function problem****
>
> ** **
>
> On Tue, Jan 31, 2012 at 12:27 PM, Edward W. Rouse <erouse(at)comsquared(dot)com>
> wrote:****
>
> And in most cases this works fine. The problem arises when invoices get
> added to the exception table due to their not being an invoice number. Even
> though we join on the tracking id, the group by on invoicenum lumps the
> different blank invoices into a single line, if the same user has more than
> 1. What we want is for each of the individual blank invoicenum entries to
> have a separate line in the result. If I remove b.invoicenum from the group
> by then I get the error in the subject line. If I try to use an aggregate
> function (like I used MAX on the names) it's even worse. MAX works on the
> names because they are all the same. MAX on the date doesn't seem to effect
> the results that I can see other than if an invoice went into exception
> more
> than once, and in that case we only want the latest one anyway.
>
> Any hints as to how to get this to not lump all of the empty invoicenums
> for
> a user into a single line? And I have mentioned putting in a dummy value
> like the date for an invoicenum, but that works as well as I expected it
> would (not at all).****
>
> ** **
>
> It seems like the use of the aggregation functions in your example aren't
> absolutely necessary - though perhaps the date one is. Best solution would
> be to get rid of the aggregate columns so that you don't have this issue.*
> ***
>
> ** **
>
> If that isn't possible, there are definitely some kludges you can use to
> get it to work if you really don't want to union 2 queries together - one
> with and one without valid invoice ids. Assuming invoice ids are generated
> via a sequence, you can do the following to guarantee a unique 'id' for
> each empty invoice - at the expense of incrementing your sequence
> unnecessarily:****
>
> ** **
>
> COALESCE(invoiceid, nextval(invoiceid_sequence))****
>
> ** **
>
> A better version of that kludge would be to create a sequence just for
> this purpose and set it to a very negative number. All of your generated
> fake ids will then be negative numbers (so easily identified by whatever is
> consuming the query results) and you can reset the sequence back to the
> most negative possible value whenever you get concerned about running out
> of ids, since you won't be using the same sequence as the invoice table
> itself.****
>
> There are probably lots of variations on that general concept. You can
> write a window function which will remember the ids already seen/generated
> for each row and just generate an arbitrary id to be used in place of null
> for grouping, though you'd have to worry about using an id that has not yet
> appeared but which later does appear. Assuming you can create a bucket of
> space large enough that is guaranteed to not conflict with valid invoice
> ids, you could make it work. I don't know if you can have select max(x),
> invoiceid group by func(invoiceid), so you may have to structure it as
> "select max(x), func(invoiceid) group by 2" which would require that your
> query results can deal with faked invoiceids - negative numbers again
> providing a possible way to identify them. ****
>
> ** **
>
> Doesn't postgres allow operator overloading? Perhaps you could override
> the equality operator for that type so that just a single value (the max
> value, for example) always compares as unequal to itself and then use
> COALESCE(invoiceid, 9223372036854775807). All rows without a valid value
> will have that value, but the overloaded equality operator will cause them
> to not group together (I'm guessing. I haven't tried this). That one makes
> me nervous because of the potential for nasty side effects should something
> legitimately have that value, but one could probably make the case for
> having a bigger problem if a column has a value equal to max bigint.****
>
> ****
>
> Restructuring the query to separate valid invoice ids from invalid and/or
> getting rid of the aggregation does seem like the best solution, though.**
> **
>