I'm doing some "statistics" (in quotes because I couldn't calculate a
standard deviation to save my life) on bills passed in our state
legislature. I have two tables, "reps" (representatives) and bills:
bf2-new=# d reps
Table "reps"
Attribute | Type | Modifier
-------------+--------------+----------
id | integer | not null
first_name | varchar(200) | not null
middle_name | varchar(200) |
last_name | varchar(200) | not null
party | varchar(20) | not null
title | varchar(200) |
elected_in | integer |
Index: reps_pkey
Constraint: ((party = 'R'::"varchar") OR (party = 'D'::"varchar"))
bf2-new=# d bills
Table "bills"
Attribute | Type | Modifier
-----------+--------------+--------------------
id | integer | not null
sub | integer | not null default 0
rep_id | integer | not null
title | varchar(400) | not null
passed | integer |
Index: bills_pkey
Constraint: ((passed = 0) OR (passed = 1))
Now, I want to get a comparison between how many bills a
representative introduced and how many of them passed. (I thought
perhaps a representative who focused on one or two bills would be
more likely to make sure it "counted.") I tried (and failed) to come
up with a good query for this, so I split it into two steps. First,
a view on how many bills were introduced & passed by each
representative:
create view bill_count_v as
select r.id as rep_id, count(*) as total, sum(passed) as passed
from reps r, bills b
where b.rep_id = r.id
group by r.id;
So far so good. Then the query:
select total as n_introduced, sum(total) as total, sum(passed) as
passed,
sum(passed)::float / sum(total) as percent
from bill_count_v
group by total;
bf2-new-# bf2-new-# bf2-new-# ERROR: Aggregate function calls may
not be nested
Doh!
It turns out that replacing "create view" with "create table" was
fine for my purposes. But is there a better way to do this that I'm
missing?