Thread: SQL problem (forgot to change header with earlier post!).
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! 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)); and I also tried to use CTEs as follows: WITH num AS ( SELECT cnt1 FROM v1 ), div AS ( SELECT cnt2 FROM v2 ) SELECT (num.cnt1/div.cnt2); Should you require any further information or if this should be on another list, please don't hesitate to contact me and/or let me know. I would appreciate a short explanation of where I'm going wrong also. TIA and rgs, Pól... ================== DDL and DML CREATE TABLE t1 (x INT); INSERT INTO t1 VALUES (4); INSERT INTO t1 VALUES (4); INSERT INTO t1 VALUES (4); INSERT INTO t1 VALUES (4); INSERT INTO t1 VALUES (4); INSERT INTO t1 VALUES (4); INSERT INTO t1 VALUES (4); INSERT INTO t1 VALUES (4); INSERT INTO t1 VALUES (4); INSERT INTO t1 VALUES (4); INSERT INTO t1 VALUES (4); INSERT INTO t1 VALUES (4); INSERT INTO t1 VALUES (4); CREATE VIEW v1 AS (SELECT COUNT(*) AS cnt1 FROM t1); CREATE TABLE t2 (y INT); INSERT INTO t2 VALUES (5); INSERT INTO t2 VALUES (5); INSERT INTO t2 VALUES (5); INSERT INTO t2 VALUES (5); INSERT INTO t2 VALUES (5); INSERT INTO t2 VALUES (5); INSERT INTO t2 VALUES (5); INSERT INTO t2 VALUES (5); INSERT INTO t2 VALUES (5); INSERT INTO t2 VALUES (5); INSERT INTO t2 VALUES (5);
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... > Todd
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... > Todd
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