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

Il 29/05/2018 13:14, Paul Linehan ha scritto:

Hi all,

I have a problem that I just can't seem to solve:

I want to divide the count of one table by the count of another -
seems simple enough!
I created simple VIEWs with counts of the tables, but I just can't
grasp the logic!

If it's not an excercise, I think you don't need them

DDL and DML (simplified) at the bottom of post.

I tried various combinations of things like basic SELECTs.

SELECT avg FROM ((SELECT cnt1 FROM v1)/(SELECT cnt2 FROM v2));

Maybe I didn't catch the problem, but

select (select count(*) from t1) / (select count(*) from t2)::float

should be a starting point (if you need an integer as a return value,
simply remove the ::float at the end

HTH
Moreno.-

Hi, and thanks for taking the trouble to reply!

WITH num AS
(
SELECT count (*) as cnt1 FROM v1
),
div AS
(
SELECT count (*) as cnt2 FROM v2
)
SELECT (num.cnt1::numeric/div.cnt2);

I get this error

ERROR: missing FROM-clause entry for table "num"
LINE 9: SELECT (num.cnt1::numeric/div.cnt2);

Check out the fiddle here -
https://dbfiddle.uk/?rdbms=postgres_10&fiddle=9fbe33f971b12ce637d03c1e7e452831

Casting as numeric just in case you might have integer division...

Yeah, forgot about the CASTing bit for the other method!

Thanks again!

Pól...

Hi and grazie for your reply!

If it's not an excercise, I think you don't need them

Not an exercise - I have to use the VIEW though - this was only a
sample. In real life the VIEW is trickier!

select (select count(*) from t1) / (select count(*) from t2)::float

Looks as if the CAST was part of it.

Check here: https://dbfiddle.uk/?rdbms=postgres_10&fiddle=9a15766de01946d7f57b4298d8fb1028

Thanks for your input!

Pól...

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).

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...

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

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