Message-ID: <3D4E4BA5.3689A1EA@technovell.com>
Date: Mon, 05 Aug 2002 11:55:49 +0200
From: Terry Yapt <yapt@technovell.com>
MIME-Version: 1.0
To: cnliou <cnliou@eurosport.com>, pgsql-sql@postgresql.org
Subject: Re: Group And Sort After Union
References: <eaa50a08.0207300046.fd57ade@posting.google.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Perhaps...

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

I hope this helps, or at least, give you a clue.  Best Regards....


I have not tested it... please

cnliou wrote:
> 
> Greetings!
> 
> 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;
> 
> Please note that the following is NOT what I want because it generates
> 2 groups of data set:
> 
> SELECT c11 AS c1,SUM(c12) AS c2 FROM table1
> GROUP BY c1
> ORDER BY c2
> UNION
> SELECT c21 AS c1,SUM(c22) AS c2 FROM table2
> GROUP BY c1
> ORDER BY c2;
> 
> How do I do that? Thank you in advance!
> 
> CNLIOU
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org