Thread: GROUP BY Wildcard Syntax Thought
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.
"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