From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jan Danielsson <jan.danielsson@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: two count columns?
Date: Fri, 17 Feb 2006 19:47:22 -0500
Message-ID: <14047.1140223642@sss.pgh.pa.us> (raw)
In-Reply-To: <43F662C4.9070402@gmail.com>
References: <43F662C4.9070402@gmail.com>
Jan Danielsson <jan.danielsson@gmail.com> writes:
> So the only thing I'm missing is the total number of log entries
> matching the from_ip, but that's where I'm stuck. My instinct is to try
> to use subqueries:
> select from_ip, count(from_ip) as entries, count(select * from log where
> ...) as tot_entries, max(ts)::timestamp(0) as last_access from log where
> to_port=22 and direction='in' group by from_ip
> ...but count() doesn't take a subquery afaik, and how would I match the
> from_ip in the inner select with the outer one?
I think you want something like
select ..., (select count(*) from log loginner where from_ip = log.from_ip)
... from log ...
You need to attach an alias to either the inner or the outer use of
"log" so that you can refer to the outer one from the inner SELECT.
Here I chose to alias the inner one, but it might be clearer to
alias the outer:
select ..., (select count(*) from log where from_ip = logouter.from_ip)
... from log logouter ...
Note that anything like this is going to be pretty expensive if your log
table is large. You might want to think about something involving
another layer of GROUP BY instead.
regards, tom lane
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: tgl@sss.pgh.pa.us, jan.danielsson@gmail.com
Subject: Re: two count columns?
In-Reply-To: <14047.1140223642@sss.pgh.pa.us>
* 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