From: | David G Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Single Table Report With Calculated Column |
Date: | 2014-08-29 13:55:55 |
Message-ID: | 1409320555622-5816886.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Kynn Jones | 2014-08-29 14:06:21 | Transforming pg_dump output to be compatible with SQLite 3.x |
Previous Message | Emmanuel Medernach | 2014-08-29 13:52:04 | Performance issue with postgres_fdw |