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