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