From: | Rich Shepard <rshepard(at)appl-ecosys(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Single Table Report With Calculated Column |
Date: | 2014-08-29 13:18:21 |
Message-ID: | alpine.LNX.2.11.1408290609300.9118@localhost |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
TIA,
Rich
From | Date | Subject | |
---|---|---|---|
Next Message | Emmanuel Medernach | 2014-08-29 13:52:04 | Performance issue with postgres_fdw |
Previous Message | David G Johnston | 2014-08-29 12:42:24 | Re: Help related to Postgresql for RHEL 6.5 |