-----Original Message----- From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql