[SQL] Help with SQL

[Eric L. Blevins]

I'm new to postgres.
I've got 2 SQL statements I would like to combine 
into one.
 
I think I need to use a sub select or join I am not 
sure.
Any help would be appreciated!
 
statement 1: SELECT  uid, count(uid)  FROM triangulated WHERE uid != 
'anonymus' AND uid!= 'anonymous' AND uid != '' GROUP BY uid ORDER BY count 
DESC LIMIT 10;
that returns something like this:
 
    uid | 
count-+--- eblevins    |  
1179 DaClyde |   
398 Drew    |    
30 zombiechick | 3(4 
rows)
statement 2: SELECT  uid, 
count(uid)  FROM points WHERE uid != 'anonymus' AND uid !='anonymous' 
AND uid != '' GROUP BY uid ORDER BY count DESC LIMIT 10;
that returns something like this: 
uid | 
count-+--- eblevins    | 
23595 DaClyde | 11031 zombiechick 
|   159 Drew    
|   104(4 rows)
 
 
what I want to do is have one statement that 
returns something like this:
uid    |    
count1    | count2
eblevins    1179    
23595
DaClyde    398    
11031
Drew    30    
104
zombiechick    3    
159
 
So everything is ordered like statement 1 but 
includes the count(uid) from the points DB like statement 2 returns
 
Any ideas on an effecient way of doing 
this?
 
 
 

Re: [SQL] Help with SQL

[Eric L. Blevins]

This is what I ended up with:

SELECT c1.uid, count1, count2
FROM (SELECT uid, count(uid) AS count1
FROM triangulated WHERE uid != 'anonymus'
AND uid != 'anonymous'
AND uid != '' GROUP BY uid) AS c1
LEFT JOIN (SELECT uid,count(uid) AS count2
FROM points
WHERE uid != 'anonymus'
AND uid != 'anonymous'
AND uid != '' GROUP BY uid) as c2 on (c1.uid = c2.uid)
ORDER BY count1 DESC LIMIT 10;

I got the results I wanted!
 uid | count1 | count2
-++
 eblevins|   1179 |  23595
 DaClyde |398 |  11031
 Drew| 30 |104
 zombiechick |  3 |159
(4 rows)

Thanks for your help!

Eric L. Blevins

- Original Message -----
From: "Oliver Elphick" <[EMAIL PROTECTED]>
To: "Eric L. Blevins" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Wednesday, October 16, 2002 4:38 PM
Subject: Re: [SQL] Help with SQL


> On Wed, 2002-10-16 at 19:26, Eric L. Blevins wrote:
> > I've got 2 SQL statements I would like to combine into one.
> ...
> > statement 1: SELECT  uid, count(uid)  FROM triangulated WHERE uid !=
'anonymus' AND uid
> > != 'anonymous' AND uid != '' GROUP BY uid ORDER BY count DESC LIMIT 10;
> ...
> > statement 2: SELECT  uid, count(uid)  FROM points WHERE uid !=
'anonymus' AND uid !=
> > 'anonymous' AND uid != '' GROUP BY uid ORDER BY count DESC LIMIT 10;
> ...
> > what I want to do is have one statement that returns something like
this:
> > uid|count1| count2
> > eblevins117923595
> > DaClyde39811031
> > Drew30104
> > zombiechick3159
> >
> > So everything is ordered like statement 1 but includes the count(uid)
from the points DB like statement 2 returns
>
> SELECT * FROM
>(SELECT uid, count(uid) AS count1
>   FROM triangulated
>  WHERE uid != 'anonymus' AND
>uid != 'anonymous' AND
>uid != ''
>  GROUP BY uid) AS c1
>LEFT JOIN
>(SELECT uid, count(uid) AS count2
>   FROM points
>  WHERE uid != 'anonymus' AND
>uid != 'anonymous' AND
>uid != ''
>  GROUP BY uid) AS c2
>   ORDER BY count1 DESC
>   LIMIT 10;
>
> (Apologies for syntax errors, if any - I haven't tried it out,)
>
> I used LEFT JOIN because you are ordering by count1, so you probably
> won't want any rows where count1 is null.  If the total of rows from
> subselect c1 was likely to be less than 10, you might want to do a FULL
> JOIN and order by count1, count2.
>
> --
> Oliver Elphick[EMAIL PROTECTED]
> Isle of Wight, UK
> http://www.lfix.co.uk/oliver
> GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
>  
>  "But be ye doers of the word, and not hearers only,
>   deceiving your own selves."  James 1:22
>
>



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]