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