Re: Help with SQL

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" <olly(at)lfix(dot)co(dot)uk>
To: "Eric L. Blevins" <eblevins(at)insight(dot)rr(dot)com>
Cc: <pgsql-sql(at)postgresql(dot)org>
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
> > 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
>
> 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 Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
> 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
>
>