On 05/29/2018 06:52 AM, Adrian Klaver wrote:
> On 05/29/2018 05:05 AM, Paul Linehan wrote:
>> Hi again, and thanks for your efforts on my behalf!
>>
>>> WITH num AS
>>> (
>>> SELECT count (*) as cnt1 FROM v1
>>> ),
>>> div AS
>>> (
>>> SELECT count (*) as cnt2 FROM v2
>>> )
>>> SELECT (num.cnt1::numeric/div.cnt2)
>>> From num cross join div;
>>
>>
>> I've tried running this code 4 different ways and none of them work -
>> your original and my efforts to tweak the code!
>>
>> This always ends up giving just 1 (integer division - using float) or
>> 1.0000000000 (using numeric).
>
> It would, each view has only a single row for the count value. From the
> fiddle:
>
> SELECT * FROM v1;
>
> cnt1
> 13
>
> SELECT * FROM v2;
>
> cnt2
> 11
>
> So doing:
>
> SELECT count (*) as cnt1 FROM v1(2)
>
> is going to return 1 in both cases and 1/1 = 1.
>
> Change:
>
> SELECT count (*) as cnt1 FROM v1
>
> SELECT count (*) as cnt2 FROM v2
>
> to
>
> SELECT cnt1 FROM v1
>
> SELECT cnt2 FROM v1
Cut and paste error, should be:
SELECT cnt2 FROM v2
>
>>
>> Check out the fiddle here:
>> https://dbfiddle.uk/?rdbms=postgres_10&fiddle=b1bd443baf16d85dee0436333a6fd919
>>
>>
>>
>>> You could have also written it like your first statement without the
>>> CTEs.
>>> This way requires joining the tables with a cross or Cartesian join.
>>
>> Yes, the first statement is the way to go on this particular case, but
>> I'm also trying to understand the ins and outs of CTEs, so I'm
>> interesting in solving this one!
>>
>>
>> Thanks again,
>>
>>
>> Rgs,
>>
>>
>> Pól...
>>
>>
>>
>>> Todd
>>
>>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
