Rich Shepard wrote > I've read some on table partitioning and using nested select statements > with group by, but have not found the syntax to produce the needed > results. > > From a table I extract row counts grouped by three columns: > > select stream, sampdate, func_feed_grp, count(*) from benthos group > by stream, sampdate, func_feed_grp order by stream, sampdate, > func_feed_group; > > And I want to include the proportion of each count based on the total rows > for each stream and sampdate. The totals are obtained with this statement: > > select stream, sampdate, count(*) as tot_cnt from benthos group by stream, > sampdate order by stream, sampdate; > > What I do not know how to do is combine the two so the resulting table > contains the columns stream, sampdate, count, proportion. I want to learn > how to build the sub-select to get this result. Joe Celko's 'SQL for > Smarties, 4th Ed.' has a close example in the chapter on table > partitioning, > but I could not apply that model to my table. You want to use window clause/function. Add the following to the first query, in the select-list: Sum(count(*)) over (partition by stream, sampdate) as stream_date_total You function counts can then be divided into this. The count(*) is because of the outer group by The sum(...) is the window function You could also just put both your queries into a with/cte (2 items) and do a normal inner join... Or Select ... From (first query) fq join (second query) sq on (...) David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Single-Table-Report-With-Calculated-Column-tp5816880p5816886.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
