| Lists: | pgsql-general |
|---|
| From: | Mark Harrison <mh(at)pixar(dot)com> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | combining two queries? |
| Date: | 2004-10-22 23:55:28 |
| Message-ID: | 41799DF0.4050502@pixar.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
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
| From: | Eddy Macnaghten <eddy(at)edlsystems(dot)com> |
|---|---|
| To: | Mark Harrison <mh(at)pixar(dot)com> |
| Cc: | pgsql-general(at)postgresql(dot)org |
| Subject: | Re: combining two queries? |
| Date: | 2004-10-23 14:18:40 |
| Message-ID: | 1098541120.30770.11.camel@localhost.localdomain |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
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
| From: | Jeffrey Melloy <jmelloy(at)visualdistortion(dot)org> |
|---|---|
| To: | eddy(at)edlsystems(dot)com |
| Cc: | Mark Harrison <mh(at)pixar(dot)com>, pgsql-general(at)postgresql(dot)org |
| Subject: | Re: combining two queries? |
| Date: | 2004-10-25 19:18:13 |
| Message-ID: | 417D5175.4080703@visualdistortion.org |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
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
>>
>>