Message-ID: <43F662C4.9070402@gmail.com>
Date: Sat, 18 Feb 2006 00:56:52 +0100
From: Jan Danielsson <jan.danielsson@gmail.com>
User-Agent: Thunderbird 1.5 (Windows/20051201)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: two count columns?
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enig6CB8AF66DE4F5E40D8078905"

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enig6CB8AF66DE4F5E40D8078905
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hello all,

   I have written a small firewall, and sshd, logging system which uses
postgresql for storing the logs.

   The table that stores the firewall logs has, among other columns,
from_ip, port and a timestamp.

I want to construct a query which returns the columns:

from_ip | port 22 entries | total entries | last_access

I have managed to put this together:

select from_ip, count(from_ip) as entries, max(ts)::timestamp(0) as
last_access from log where to_port=3D22 and direction=3D'in' group by fro=
m_ip

   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
=2E..) as tot_entries, max(ts)::timestamp(0) as last_access from log wher=
e
to_port=3D22 and direction=3D'in' group by from_ip

=2E.but count() doesn't take a subquery afaik, and how would I match the
from_ip in the inner select with the outer one? So I assume that
subqueries aren't the way yo go. Is there even a query to return those
columns in that configuration?

Thankful for any hints or tips.

--=20
Kind Regards,
Jan Danielsson
Te audire non possum. Musa sapientum fixa est in aure.


--------------enig6CB8AF66DE4F5E40D8078905
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2.1 (MingW32)

iD8DBQFD9mLE8wBCTJQ8HEIRAoo+AJ44yB6WRXo/cvUGomP1aI4q9tOWbgCfT9kC
sq+iZ5HXTrjm0X/RwGWIkmE=
=gkbm
-----END PGP SIGNATURE-----

--------------enig6CB8AF66DE4F5E40D8078905--