From: Eric L. Blevins <eblevins@insight.rr.com>
To: Oliver Elphick <olly@lfix.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: Help with SQL
Date: Wed, 16 Oct 2002 17:01:12 -0400
Message-ID: <00c301c27557$2c2cc010$0201a8c0@Gateway> (raw)
References: <009c01c27541$83bf46b0$0201a8c0@Gateway>
<1034800739.22821.105.camel@linda>
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@lfix.co.uk>
To: "Eric L. Blevins" <eblevins@insight.rr.com>
Cc: <pgsql-sql@postgresql.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.Elphick@lfix.co.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
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: eblevins@insight.rr.com, olly@lfix.co.uk
Subject: Re: Help with SQL
In-Reply-To: <00c301c27557$2c2cc010$0201a8c0@Gateway>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox