Postgres Pro
Downloads
Home   >   mailing lists

Thread: UNION?

UNION?

From
Forest Felling
Date:
I am trying to teach myself PostgreSQL. I have scant programming background, but a great deal of experience using "off-the-shelf" databases.

It seems there ought to be a way to eliminate the create view, and get the total of the codes found with the queries below. (I've learned that "Aggregate function calls may not be nested," among other things.)

My son, a senior SQL programmer with Teleperformance USA in SLC suggested a union clause; however his (SQL Server) syntax fails. I can't figure how that clause can be applied from the books I have.

Suggestions will be appreciated!
Will also appreciate suggestions on articles which may help me understand the union clause more fully.

Thanks!

mydb=# create view single_code as
        select count(city), code from test_year
        where length(code) = 1
        group by code order by code;
CREATE
mydb=# select * from single_code;
 count | code
-------+------
     1 | -
     1 | 2
     2 | A
    63 | C
    11 | G
   224 | H
     1 | L
  6363 | N
   542 | Q
   180 | T
   820 | V
     2 | n
(12 rows)

mydb=# select sum(count) from single_code;
 sum 
------
 8210
(1 row)

Suggested code regarding union clause, with result:

mydb=# select count(city) as qty, code as code
  from test_year
  where length(code) = 1
  group by code
   order by code
   union all
       select count(city) as qty, 'All' as code
        from test_year
        where length(code) = 1
ERROR:  parser: parse error at or near "all"
Drop ALL, and get
ERROR:  parser: parse error at or near "select"
--
Forest W. Felling     e-mail:  forest@oldgrumpy.com
                          Web: http://www.oldgrumpy.com

Ask me about "Full Spectrum Nutrition!"

Re: UNION?

From
Manfred Koizar
Date:
On Mon, 20 Jan 2003 08:52:09 -0500, Forest Felling
<res08i7v@verizon.net> wrote:
>mydb=# select count(city) as qty, code as code
>    from test_year
>    where length(code) = 1
>    group by code
>    order by code
>    union all
>    select count(city) as qty, 'All' as code
>    from test_year
>    where length(code) = 1
>ERROR:  parser: parse error at or near "all"
>Drop ALL, and get
>ERROR:  parser: parse error at or near "select"

Try
    SELECT * FROM (
        SELECT ...
          FROM test_year ...
         GROUP BY ...
         ORDER BY ... ) AS t1
    UNION ALL
    SELECT count(...), 'All' ...

or
    SELECT 1, count(city) as qty, code as code
      FROM ...
     GROUP BY
    UNION ALL
    SELECT 2, count(city) as qty, 'All' as code
      FROM ...
     ORDER BY 1, 3;

Servus
 Manfred

Re: UNION?

From
Tom Lane
Date:
Forest Felling <res08i7v@verizon.net> writes:
> mydb=# select count(city) as qty, code as code
>     from test_year
>     where length(code) = 1
>     group by code
>     order by code
>     union all
>     select count(city) as qty, 'All' as code
>     from test_year
>     where length(code) = 1
> ERROR:  parser: parse error at or near "all"

If you want to use ORDER BY in one of the elements of a UNION (which is
not legal per standard SQL), you need to parenthesize:

(select count(city) as qty, code as code
    from test_year
    where length(code) = 1
    group by code
    order by code)
union all
(select count(city) as qty, 'All' as code
    from test_year
    where length(code) = 1)

The reason for this can be seen by considering this variant query:

(select count(city) as qty, 'All' as code
    from test_year
    where length(code) = 1)
union all
(select count(city) as qty, code as code
    from test_year
    where length(code) = 1
    group by code
    order by code)

Without the parens, it would be unclear whether you mean the ORDER BY
to apply to the second sub-SELECT, or to the result of the whole UNION.
(SQL spec would require it to be interpreted as applying to the whole
UNION result, I believe.)

            regards, tom lane