Thread: 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

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.

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

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.klaver@aklaver.com

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

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.klaver@aklaver.com