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