pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMultiple counts
3+ messages / 3 participants
[nested] [flat]
* Multiple counts
@ 2001-12-27 05:36 Richard Rowell <rwrowell@bellsouth.net>
0 siblings, 1 reply; 3+ messages in thread
From: Richard Rowell @ 2001-12-27 05:36 UTC (permalink / raw)
To: pgsql-sql
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,
...)
First off, please note that the "call" table will get large ( growing by up
to 6k records per week) and the status table will grow linearly with it (n*20
or so).
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
But all my lame attempts at getting both in the same query fail.
TIA
_________________________
Richard Rowell
rwrowell@bellsouth.net
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Multiple counts
@ 2001-12-29 01:15 Andrew G. Hammond <drew@xyzzy.dhs.org>
parent: Richard Rowell <rwrowell@bellsouth.net>
0 siblings, 0 replies; 3+ messages in thread
From: Andrew G. Hammond @ 2001-12-29 01:15 UTC (permalink / raw)
To: Richard Rowell <rwrowell@bellsouth.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Multiple counts
@ 2002-12-29 04:55 Colin Fox <cfox@cfconsulting.ca>
0 siblings, 0 replies; 3+ messages in thread
From: Colin Fox @ 2002-12-29 04:55 UTC (permalink / raw)
To: pgsql-sql
Hi, all.
I'm trying to calculate two count()s.
I have a campaigns table, a campaign_parts table and a people table.
Here is a simplified view of 3 tables I'm trying to use:
create table campaigns {id serial, company_id int, name varchar(20));
create table campaign_parts(id serial, campaign_id int, name varchar(20));
create table people (id serial, campaignpart_id int, name varchar(20));
(fk references ignored for brevity).
I'd like to create a query which shows how many campaign parts there are
for a given set of campaigns, and how many people signed up via a campaign
(which means a sum of all the signups under all the parts of that
campaign).
The naive way to do this would be something like:
select
camp.id,
camp.name,
count(cp.id) as numparts,
count(p.id) as numsignups
from
campaigns camp,
campaign_parts cp,
people p
where
camp.company_id = <some value> and
cp.campaign_id = camp.id and
p.cpid = cp.id
group by
camp.id, camp.name;
(I know this is really naive, but I hope it shows what I'm trying to do).
However, there are some problems with this. First, if there are no
campaign parts, I'd like to show a 0, but then that campaign doesn't
show up at all. So I need to UNION it with another selection.
Also, the counting doesn't seem to be working. I'm getting a multiple of
the correct answer.
Please help!
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-12-29 04:55 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-12-27 05:36 Multiple counts Richard Rowell <rwrowell@bellsouth.net>
2001-12-29 01:15 ` Andrew G. Hammond <drew@xyzzy.dhs.org>
2002-12-29 04:55 Multiple counts Colin Fox <cfox@cfconsulting.ca>
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