Subject: Re: Multiple counts
From: "Andrew G. Hammond" <drew@xyzzy.dhs.org>
To: Richard Rowell <rwrowell@bellsouth.net>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <20011227053930.DMEM18093.imf07bis.bellsouth.net@there>
References: <20011227053930.DMEM18093.imf07bis.bellsouth.net@there>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="=-DBOCehGSClBEqWzeomb+"
Date: 28 Dec 2001 20:15:26 -0500
Message-Id: <1009588527.1033.0.camel@xyzzy>
Mime-Version: 1.0

--=-DBOCehGSClBEqWzeomb+
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

On Thu, 2001-12-27 at 00:36, Richard Rowell wrote:
> I have a one-to-many relationship with a table structure like so:
>=20
> create table call(
> uid	serial,
> store	int,
> ...)
>=20
> create table status(
> call_uid	int,
> status_uid int,
> ...)
> With that in mind, what I am trying for is an efficient query to get a tu=
ple=20
> set like so:
> store	number_of_calls	number_of_status_where_statusuid_iszero
>=20
> I can get 1&2:
> select store, count(*) from call group by store;
>=20
> or 1&3:
> select c.store,count(s.status_uid)=20
> from call c, status s=20
> where s.call_uid=3Dc.uid=20
>   and s.status_uid=3D0=20
> group by c.store

  From your schema, it looks like you're keeping history of a call's
status instead of just the current status.  If that is the case, then
your second query above will not report the correct answer as it will
not recognize situations where the call is now resolved.

  The correct query in this instance is as follows:

SELECT c.store, count(*) AS unresolved=20
FROM call c, (SELECT DISTINCT ON (call_uid) call_uid, status_uid
  FROM status ORDER BY call_uid, uid DESC) s
WHERE c.uid =3D s.call_uid AND s.status_uid =3D 0=20
GROUP BY c.store

  This can perhaps be made more efficient by creating a view and some
indices:

CREATE INDEX foo ON status (call_uid)

CREATE VEIW call_status AS
SELECT DISTINCT ON (s.call_uid) c.store, c.uid, s.status_uid
FROM call c, status s
WHERE c.uid =3D s.call_uid
ORDER BY s.call_uid, s.uid DESC

CREATE INDEX bar ON call_status (store);

SELECT store, count(*) AS unresolved
FROM call_status
WHERE status =3D 0
GROUP BY store

  If you are not interested in keeping call history, then I'd suggest
moving the status information into the call table as it will simplify
your schema somewhat.

  As for joining the two queries into one, I don't think there's a
clever way to do it, unless you do something like the following (if
you've got 7.2):

SELECT store, count(uid) AS calls=20
FROM call=20
GROUP BY store
LEFT OUTER JOIN USING store
SELECT store, count(*) AS unresolved=20
FROM call_status=20
WHERE status =3D 0=20
GROUP BY store
ORDER BY store

or, if you're not messing around with the latest and greatest yet,

SELECT c.store, c.calls, u.unresolved
FROM (SELECT store, count(*) AS calls FROM call GROUP BY store) c,
  (SELECT store, count(*) AS unresolved FROM call_status=20
   WHERE status =3D 0 GROUP BY store) u
WHERE c.store =3D u.store
UNION ALL
SELECT c.store, c.calls, 0 as unresolved
FROM (SELECT store, count(*) AS calls FROM call GROUP BY store) c
WHERE c.store NOT IN (SELECT store FROM call_status WHERE status =3D 0)
ORDER BY store

  There may be a better way to do this by writing a special purpose
aggregate function, but I can't think of it off hand.

--=20
Andrew G. Hammond     mailto:drew@xyzzy.dhs.org=20=20
http://xyzzy.dhs.org/~drew/
56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20
613-389-5481
5CD3 62B0 254B DEB1 86E0  8959 093E F70A B457 84B1
"To blow recursion you must first blow recur" -- me

--=-DBOCehGSClBEqWzeomb+
Content-Type: application/pgp-signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iQCVAwUAPC0ZLcaBTSA3ULb1AQJFawP/fHB4icfaA9f8LFn5SVtsE2QHx9YrjfAC
3B3hq71t1P0ezzLFYNlxcVa94O2WgJ10vSpG0u8Jdg652mvifXsRJ3+ee7q1fbL2
Jo9Eu0xhixyAF8gpvv2qMzLCTXrIMGfbGiZBxPlhrKsP90DjXK8+nhmZ2Y+38ClK
KZXoUKuhEZk=
=LuR7
-----END PGP SIGNATURE-----

--=-DBOCehGSClBEqWzeomb+--