GROUP BY Wildcard Syntax Thought

Started by David G. Johnstonalmost 15 years ago2 messagesgeneral
Jump to latest
#1David G. Johnston
david.g.johnston@gmail.com

When specifying columns in a GROUP BY clause would it be possible to use a
wildcard to specify all columns coming from a given relation?

SELECT

rosum.*,

sum(ld.amount) AS ldcost,

count(ld.amount) AS ldcount,

rosum.rocost + sum(ld.amount) AS netbal

FROM (

SELECT w.s_id, w.accountnumber, w.date_reference, w.invoicenumber,

sum(w.amount_cost) AS rocost, count(w.amount_cost) AS rocount

FROM wiplaboradpstaging w

WHERE <FILTER>

GROUP BY w.s_id, w.accountnumber, w.date_reference, w.invoicenumber

) rosum

LEFT JOIN ledgerdetail ld ON (

rosum.s_id = ld.s_id AND

rosum.accountnumber = ld.accountnumber AND

rosum.invoicenumber = ld.reference

)

GROUP BY rosum.* < ------- WildCard Group By Since the rosum sub-query has
already been subjected to a GROUP BY

Attempt to run this query in 9.0.3 results in:

"SQL Error: ERROR: column "rosum.s_id" must appear in the GROUP BY clause
or be used in an aggregate function

LINE 2: rosum.*,"

David J.

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: David G. Johnston (#1)
Re: GROUP BY Wildcard Syntax Thought

"David Johnston" <polobo@yahoo.com> writes:

When specifying columns in a GROUP BY clause would it be possible to use a
wildcard to specify all columns coming from a given relation?

I think the need for this will be largely superseded by the SQL-standard
behavior that grouping by a primary key is sufficient (which we've
finally implemented as of 9.1).

regards, tom lane