Re: Question About Aggregate Functions

El mar, 12-09-2006 a las 21:15 -0400, Don Parris escribió:

> I'm a DB novice as well as a pgsql novice. I can manage to
> run a few basic queries, but that's all I've really done so
> far. How do I create a query that (1) evaluates each boolean
> field for TRUE/FALSE and (2) counts the number of rows where
> each field is TRUE? Also, one field is an integer, so I want
> the average from that field, rather than the count. Something
> along the lines of:
>
> SELECT COUNT (t1.fielda, t2.fielda, t2.fieldb) AVG(t2.fieldc)
> FROM t1, t2
> WHERE t1.item_id=t2.item_id AND t1.fielda IS TRUE AND
> t2.fielda IS TRUE AND t2.fieldb IS TRUE AND t2.fieldc IS NOT
> NULL
>
> The result is intended to be something of a summary report.
> t1
>
> SELECT count(t1.fielda), count(t2.fielda), count(t2.fieldb),
> AVG(t2.fieldc)
> FROM t1 JOIN t2 ON t1.item_id = t2.item_id
> GROUP BY NULL;

>
> This one looks more like what I am attempting to do. However, I do
> need to be sure my count() functions are counting the values that are
> TRUE. Is this a case where I should run a query to select the records
> where the values for the desired columns are true, insert that result
> into a temp table, and then perform the count() function as above on
> just those records? Sure seems like that would be the simple route,
> now that I think about it.

You should then use WHERE clause:

SELECT count(t1.fielda) FROM t1 WHERE t1.fielda = TRUE;

this should report how many rows of t1 are TRUE.

BTW, I cannot understand your query's logic because you want to
aggregate both tables.

Joinning both tables you get a table like:

item_id, t1.fielda, t2.fielda, t2.fieldb, t2.fieldc

Then you want to aggregate fields _independently_ so you may need to do
four subqueries.

At last, if you use aggregate functions without group by clause,
postgres use data from the whole table and returns only one row with
that result.

A query (UNTESTED) that should solve your problem may be this one:

SELECT a.count AS "t1fielda",
b.count AS "t2fielda",
c.count AS "t2fieldb",
d.avg AS "t2avgc"
FROM (SELECT count(*) FROM t1 WHERE fielda = TRUE) AS a
JOIN (SELECT count(*) FROM t2 WHERE fielda = TRUE) AS b ON TRUE
JOIN (SELECT count(*) FROM t2 WHERE fieldb = TRUE) AS c ON TRUE
JOIN (SELECT avg(fieldc) FROM t2) AS d ON TRUE;

I am also a DB novice so this is correct.

Regards

>
>