Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux