Re: Question About Aggregate Functions

On 9/12/06, Brandon Aiken <BAiken(at)winemantech(dot)com> wrote:
>
> First, aggregate functions always have to have a GROUP BY clause. If you
> want everything in a table or join, you use GROUP BY NULL.
>

Thanks. I did not realize that.

Next, IS TRUE statements will select anything that is not NULL, 0, or FALSE,
> so I'm not sure what you're trying to get because you're getting nearly
> everything, and count() already ignores NULL values.
>

I didn't see that in the manual's coverage, but could have overlooked it.
But count() will include the FALSE values along with the TRUE values -
ignoring only those that are NULL. At least, I think that's the case. So,
for each column I select, I need to be sure I am counting only the TRUE
values. I do have NULL, FALSE and TRUE values in each column, since I do
not always know for sure whether an attribute is TRUE or FALSE when I record
the item. That may be determined later, but not in all cases.

Next, count(x, y, z) isn't a valid function. Count() only has one
> parameter, so you'll have to call it several times.
>

I knew my syntax was wrong - but wasn't sure about calling multiple
functions since I hadn't seen any examples of that in my hunting for info.
I was trying to make a little clearer what I wanted to do.

Depending on what you were hoping count(x, y, z) was returning, you do this:
>
>
>
>
> SELECT count(t1.fielda), count(t2.fielda), count(t2.fieldb), AVG(t2.fieldc
> )
>
> FROM t1 JOIN t2 ON 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.

<SNIP>

> Greetings,
>
> 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 contains
> basic info about each item, with one field for whether the item is active or
> not. The other table contains additional info about whether each item has
> particular attributes. I want the query to tell me the average number of
> years active items have been active, and the number of items where each
> attribute is true. I then want to turn the raw attribute counts into
> percentages, so I can say "for n% of the items these attributes are true".
>
> Pointers to good examples/tutorials are welcome. Most I have seen are
> rather simplistic, and what I am reading in the manual isn't coming together
> very well.
>
> Thanks,
> Don
>