Re: SQL problem (forgot to change header with earlier post!).

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(dot)klaver(at)aklaver(dot)com