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

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