pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp with SQL
3+ messages / 2 participants
[nested] [flat]
* Help with SQL
@ 2002-10-16 18:26 Eric L. Blevins <eblevins@insight.rr.com>
0 siblings, 1 reply; 3+ messages in thread
From: Eric L. Blevins @ 2002-10-16 18:26 UTC (permalink / raw)
To: pgsql-sql
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?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Help with SQL
@ 2002-10-16 20:38 Oliver Elphick <olly@lfix.co.uk>
parent: Eric L. Blevins <eblevins@insight.rr.com>
0 siblings, 1 reply; 3+ messages in thread
From: Oliver Elphick @ 2002-10-16 20:38 UTC (permalink / raw)
To: Eric L. Blevins <eblevins@insight.rr.com>; +Cc: pgsql-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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Help with SQL
@ 2002-10-16 21:01 Eric L. Blevins <eblevins@insight.rr.com>
parent: Oliver Elphick <olly@lfix.co.uk>
0 siblings, 0 replies; 3+ messages in thread
From: Eric L. Blevins @ 2002-10-16 21:01 UTC (permalink / raw)
To: Oliver Elphick <olly@lfix.co.uk>; +Cc: pgsql-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@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
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-10-16 21:01 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-10-16 18:26 Help with SQL Eric L. Blevins <eblevins@insight.rr.com>
2002-10-16 20:38 ` Oliver Elphick <olly@lfix.co.uk>
2002-10-16 21:01 ` Eric L. Blevins <eblevins@insight.rr.com>
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