Thread: need some more select help

I'm trying to

select a,count(1) FROM (select a from X UNION select a from Y ) group by a;

but it complains and says that the subquery must have an alias and I
have not figured it out how to add that.

Btw. is there any other way of doing what I'm doing above?

It's basically    select a,count(1) from X group by a     +  select
a,count(1) from Y  group by a  where  "+" means they should be summed
on the "a" field.

I tried
select a,sum(b)   from ( select a,count(1) as b from X group by a
union select a,count(1) as b from Y group by a) group by a

but that gave also an error asking for an alias for the subquery.

How can I solv this problem?

> As the error indicates, you must give an alias for subquery
>
> select a,count(1) FROM (select a from X UNION select a from Y ) as
> your_alias_name group by a;

Ohh, I see. I completly misunderstood it earlier.
Thanks for clearing that up :-)

Hello,

As the error indicates, you must give an alias for subquery

select a,count(1) FROM (select a from X UNION select a from Y ) as
your_alias_name group by a;





--
.regards
.thara s pillai

You just need "AS x" x after the subquery.

However I think you have other problems with these queries. UNION
eliminates duplicates so your group by will only ever give a count of 1.

You could use UNION ALL. or you might use two separate grouped
subqueries and the sum the two together.

--
Greg

On 2009-07-17, at 9:23 AM, A B <gentosaker@gmail.com> wrote:

> I'm trying to
>
> select a,count(1) FROM (select a from X UNION select a from Y )
> group by a;
>
> but it complains and says that the subquery must have an alias and I
> have not figured it out how to add that.
>
> Btw. is there any other way of doing what I'm doing above?
>
> It's basically    select a,count(1) from X group by a     +  select
> a,count(1) from Y  group by a  where  "+" means they should be summed
> on the "a" field.
>
> I tried
> select a,sum(b)   from ( select a,count(1) as b from X group by a
> union select a,count(1) as b from Y group by a) group by a
>
> but that gave also an error asking for an alias for the subquery.
>
> How can I solv this problem?
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice

Hello,

As the error indicates, you must give an alias for subquery

select a,count(1) FROM (select a from X UNION select a from Y ) as
your_alias_name group by a;


----- Original Message -----
From: "A B" <gentosaker@gmail.com>
To: <pgsql-novice@postgresql.org>
Sent: Friday, July 17, 2009 01:53 PM
Subject: [NOVICE] need some more select help


> I'm trying to
>
> select a,count(1) FROM (select a from X UNION select a from Y ) group by
> a;
>
> but it complains and says that the subquery must have an alias and I
> have not figured it out how to add that.
>
> Btw. is there any other way of doing what I'm doing above?
>
> It's basically    select a,count(1) from X group by a     +  select
> a,count(1) from Y  group by a  where  "+" means they should be summed
> on the "a" field.
>
> I tried
> select a,sum(b)   from ( select a,count(1) as b from X group by a
> union select a,count(1) as b from Y group by a) group by a
>
> but that gave also an error asking for an alias for the subquery.
>
> How can I solv this problem?
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice


______________________________________
Scanned and protected by Email scanner