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