Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id C91C847616B
	for <pgsql-sql@postgresql.org>; Fri,  2 Aug 2002 09:47:26 -0400 (EDT)
Received: from sss.pgh.pa.us (unknown [192.204.191.242])
	by postgresql.org (Postfix) with ESMTP id 51080475C92
	for <pgsql-sql@postgresql.org>; Fri,  2 Aug 2002 09:47:24 -0400 (EDT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.12.5/8.12.5) with ESMTP id g72DlKVk029428;
	Fri, 2 Aug 2002 09:47:21 -0400 (EDT)
To: cnliou@eurosport.com (cnliou)
Cc: pgsql-sql@postgresql.org
Subject: Re: Group And Sort After Union 
In-reply-to: <eaa50a08.0207300046.fd57ade@posting.google.com> 
References: <eaa50a08.0207300046.fd57ade@posting.google.com>
Comments: In-reply-to cnliou@eurosport.com (cnliou)
	message dated "30 Jul 2002 01:46:22 -0700"
Date: Fri, 02 Aug 2002 09:47:20 -0400
Message-ID: <29427.1028296040@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200208/33
X-Sequence-Number: 8879

cnliou@eurosport.com (cnliou) writes:
> I want to GROUP BY and ORDER BY on the result of UNION similar to the
> following (wrong) SQL:

> (SELECT c11 AS c1,SUM(c12) AS c2 FROM table1
> UNION
> SELECT c21 AS c1,SUM(c22) AS c2 FROM table2
> )
> GROUP BY c1
> ORDER BY c2;

Correct is

SELECT * FROM
(SELECT c11 AS c1,SUM(c12) AS c2 FROM table1
UNION
SELECT c21 AS c1,SUM(c22) AS c2 FROM table2
) ss
GROUP BY c1
ORDER BY c2;

			regards, tom lane