Обсуждение: combining two queries?

Поиск
Список
Период
Сортировка

combining two queries?

От
Mark Harrison
Дата:
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

Re: combining two queries?

От
Duane Lee - EGOVX
Дата:

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

Re: combining two queries?

От
Eddy Macnaghten
Дата:
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

Re: combining two queries?

От
Jeffrey Melloy
Дата:
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:

>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
>>
>>