combining two queries?

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@pixar.com]
Sent: Friday, October 22, 2004 4:55 PM
To: pgsql-general@postgresql.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:

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

--
Edward A. Macnaghten
http://www.edlsystems.com

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: