To: Jan Danielsson <jan.danielsson@gmail.com>
cc: pgsql-sql@postgresql.org
Subject: Re: two count columns? 
In-reply-to: <43F662C4.9070402@gmail.com> 
References: <43F662C4.9070402@gmail.com>
Comments: In-reply-to Jan Danielsson <jan.danielsson@gmail.com>
	message dated "Sat, 18 Feb 2006 00:56:52 +0100"
Date: Fri, 17 Feb 2006 19:47:22 -0500
Message-ID: <14047.1140223642@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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