From: Andrew G. Hammond <drew@xyzzy.dhs.org>
To: Richard Rowell <rwrowell@bellsouth.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Multiple counts
Date: 28 Dec 2001 20:15:26 -0500
Message-ID: <1009588527.1033.0.camel@xyzzy> (raw)
In-Reply-To: <20011227053930.DMEM18093.imf07bis.bellsouth.net@there>
References: <20011227053930.DMEM18093.imf07bis.bellsouth.net@there>
On Thu, 2001-12-27 at 00:36, Richard Rowell wrote:
> I have a one-to-many relationship with a table structure like so:
>
> create table call(
> uid serial,
> store int,
> ...)
>
> 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 tuple
> set like so:
> store number_of_calls number_of_status_where_statusuid_iszero
>
> I can get 1&2:
> select store, count(*) from call group by store;
>
> or 1&3:
> select c.store,count(s.status_uid)
> from call c, status s
> where s.call_uid=c.uid
> and s.status_uid=0
> 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
FROM call c, (SELECT DISTINCT ON (call_uid) call_uid, status_uid
FROM status ORDER BY call_uid, uid DESC) s
WHERE c.uid = s.call_uid AND s.status_uid = 0
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 = 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 = 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
FROM call
GROUP BY store
LEFT OUTER JOIN USING store
SELECT store, count(*) AS unresolved
FROM call_status
WHERE status = 0
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
WHERE status = 0 GROUP BY store) u
WHERE c.store = 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 = 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.
--
Andrew G. Hammond mailto:drew@xyzzy.dhs.org
http://xyzzy.dhs.org/~drew/
56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F
613-389-5481
5CD3 62B0 254B DEB1 86E0 8959 093E F70A B457 84B1
"To blow recursion you must first blow recur" -- me
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: drew@xyzzy.dhs.org, rwrowell@bellsouth.net
Subject: Re: Multiple counts
In-Reply-To: <1009588527.1033.0.camel@xyzzy>
* 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