How can I combine these two queries?

# select viewerid,count(*) from viewer_movies group by viewerid order by viewerid;
viewerid | count
----------+--------
22964835 | 3055
22964836 | 1291
22964837 | 3105
22964838 | 199


planb=# select name from xenons where id = 23500637;
name
---------
x.moray

I would like to end up with a query result like this:

viewer | count
----------+--------
x.surf | 3055
x.dream | 1291
x.moray | 3105
x.sleepy | 199

Many TIA!
Mark

--
Mark Harrison
Pixar Animation Studios

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Try

select a.name,count(*) from
xenons as a,
viewer_movies as b
where a.id = b.viewerid
group by a.name order by a.name;

-----Original Message-----
From: Mark Harrison [mailto:mh (AT) pixar (DOT) com]
Sent: Friday, October 22, 2004 4:55 PM
To: pgsql-general (AT) postgresql (DOT) org
Subject: [GENERAL] combining two queries?

How can I combine these two queries?

# select viewerid,count(*) from viewer_movies group by viewerid order by
viewerid;
viewerid | count
----------+--------
22964835 | 3055
22964836 | 1291
22964837 | 3105
22964838 | 199


planb=# select name from xenons where id = 23500637;
name
---------
x.moray

I would like to end up with a query result like this:

viewer | count
----------+--------
x.surf | 3055
x.dream | 1291
x.moray | 3105
x.sleepy | 199

Many TIA!
Mark

--
Mark Harrison
Pixar Animation Studios

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

select b.name as viewer, count(*)
from viewer_movies a, xenons b
where b.id = a.viewerid
group by b.name


On Sat, 2004-10-23 at 00:55, Mark Harrison wrote:
Edward A. Macnaghten
http://www.edlsystems.com


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

If you want to return rows with zeros, you may need to do something like
this:

select b.name as viewer, count(viewerid)
from xenons b left join viewer_movies a on (b.id = a.viewerid)
group by b.name

Eddy Macnaghten wrote:


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly