Re: [GENERAL] Single Table Report With Calculated Column
On 08/29/2014 10:15 AM, Rich Shepard wrote: On Fri, 29 Aug 2014, Adrian Klaver wrote: I am going to assume you mean Postgres did not like the syntax. Adrian, Oops! Mea culpa. Yes, postgres. What was the error message you got back? I don't recall. It was yesterday afternoon and I flushed it from memory when it did not work. Assuming you did this in psql, looking in ~/.psql_history might be a good way to retrieve what you did and then use that to rerun the query. Rich -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Single Table Report With Calculated Column
On Fri, 29 Aug 2014, Adrian Klaver wrote: I am going to assume you mean Postgres did not like the syntax. Adrian, Oops! Mea culpa. Yes, postgres. What was the error message you got back? I don't recall. It was yesterday afternoon and I flushed it from memory when it did not work. Rich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Single Table Report With Calculated Column
On 08/29/2014 09:50 AM, Rich Shepard wrote: On Fri, 29 Aug 2014, David G Johnston wrote: You want to use window clause/function. David, I read about this, but did not absorb everything. 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 So this is a way of combinging aggregates; I tried something like the above but postfix did not like my syntax; it was not using partitions. I am going to assume you mean Postgres did not like the syntax. What was the error message you got back? 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 (...) Both of which need more study on my part. I read about CTEs and did not then see how to apply them to my needs. Many thanks, Rich -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Single Table Report With Calculated Column
On Fri, 29 Aug 2014, David G Johnston wrote: You want to use window clause/function. David, I read about this, but did not absorb everything. 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 So this is a way of combinging aggregates; I tried something like the above but postfix did not like my syntax; it was not using partitions. 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 (...) Both of which need more study on my part. I read about CTEs and did not then see how to apply them to my needs. Many thanks, Rich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Single Table Report With Calculated Column
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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Single Table Report With Calculated Column
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
