Re: Multiple counts

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(at)xyzzy(dot)dhs(dot)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