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

[Paul Linehan <linehanp@xxxxxx>]

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