Skip site navigation (1) Skip section navigation (2)

Question About Aggregate Functions

From: "Don Parris" <parrisdc(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Question About Aggregate Functions
Date: 2006-09-12 16:47:03
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-generalpgsql-novice

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.



pgsql-novice by date

Next:From: Don ParrisDate: 2006-09-13 01:15:37
Subject: Re: Question About Aggregate Functions
Previous:From: Elias ChavarriaDate: 2006-09-12 15:55:08
Subject: Startup script problem

pgsql-general by date

Next:From: Tom LaneDate: 2006-09-12 16:47:17
Subject: Re: Superuser lost access to particular database
Previous:From: Tony CadutoDate: 2006-09-12 16:40:25
Subject: Re: Stored Procedure performance / elegance question

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group