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

Attempting to get a list of items that we want to be grouped by id and date,
with a detail column.

Table a is a typical user table; id, first and last name are all that I am
using

Table b is a tracking table, combining data from 8+ tables into one for ease
of use for reporting purposes; id, userid, company and invoice_number are
all that I am using

Table c is an exception table; it has relations with the other 2 by both the
tracking and user ids but all I need from it is the exception date

So the data required is the users first and last name, the company, the
invoice number and the exception date. And the expected structure for the
report is:

User Name: Company: Date: Invoice Number

For those invoices that are in the exception table. The sql I can get to
work is:

SELECT MAX(a.name_first) AS name_first, MAX(a.name_last) AS name_last,
b.company, MAX(c.report_date) AS rDate, b.invoicenum
FROM resources a JOIN tracking b ON (a.id=b.resource_id)JOIN except_detail c ON (b. id = b.tracking_id)
WHERE b.region = NE' AND b.state = 1
GROUP BY a.id, b. company, b.invoicenum
ORDER BY name_last, name_first, b.role_name, rDate 

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).

Edward W. Rouse

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Edward W. Rouse
Sent: Tuesday, January 31, 2012 3:27 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] must appear in the GROUP BY clause or be used in an aggregate
function problem

Attempting to get a list of items that we want to be grouped by id and date,
with a detail column.

Table a is a typical user table; id, first and last name are all that I am
using

Table b is a tracking table, combining data from 8+ tables into one for ease
of use for reporting purposes; id, userid, company and invoice_number are
all that I am using

Table c is an exception table; it has relations with the other 2 by both the
tracking and user ids but all I need from it is the exception date

So the data required is the users first and last name, the company, the
invoice number and the exception date. And the expected structure for the
report is:

User Name: Company: Date: Invoice Number

For those invoices that are in the exception table. The sql I can get to
work is:

SELECT MAX(a.name_first) AS name_first, MAX(a.name_last) AS name_last,
b.company, MAX(c.report_date) AS rDate, b.invoicenum FROM resources a JOIN
tracking b ON (a.id=b.resource_id)JOIN except_detail c ON (b. id = b.tracking_id) WHERE b.region = NE'
AND b.state = 1 GROUP BY a.id, b. company, b.invoicenum ORDER BY name_last,
name_first, b.role_name, rDate 

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).

Edward W. Rouse

----------------------------------------------------------------------------
---------------

Edward,

I would suggest first processing all exceptions EXCEPT "missing invoice
number" in one query, then constructing a second query that deals only with
missing invoice numbers, and finally UNION-ing the two results.

Also, I dislike the "MAX(varchar)" semantics.  You should be able to write
the query without it but without more details I cannot readily specify how.
Generally you'd simply operate off of "id" and then join in the
corresponding first/last names at the outer-most layer of the query.  Since
you are grouping on "a.id" anyway I would suggest you try it.

David J.