Postgres Pro
Downloads
Home   >   mailing lists

Thread: Group And Sort After Union

Group And Sort After Union

From
cnliou@eurosport.com (cnliou)
Date:
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

Re: Group And Sort After Union

From
Tom Lane
Date:
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

Re: Group And Sort After Union

From
Terry Yapt
Date:
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