Re: Question About Aggregate Functions - Mailing list pgsql-novice
| From | Don Parris |
|---|---|
| Subject | Re: Question About Aggregate Functions |
| Date | |
| Msg-id | 1eba300b0609121815p6def0998ibcc3555a9d5d0e80@mail.gmail.com Whole thread Raw |
| In response to | Question About Aggregate Functions ("Don Parris" <parrisdc@gmail.com>) |
| Responses |
Re: Question About Aggregate Functions
Re: Question About Aggregate Functions |
| List | pgsql-novice |
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.
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
pgsql-novice by date: