Update Table T1 column to be the quotient of two queries each leveraging ID from T1-CodePudding

I am trying to update a column to be the quotient of two queries that count records in two other tables for each ID in T1.

It seems like this should work (but doesn't):

Update T1 Set COLUMN1 = (select count(*) from T2 where T2.ID = T1.ID) / (select count(*) from T3 where T3.ID = T1.ID)

Edit to add data sample and expected output:

T1 is like:

ID	COLUMN1
0
1

T2 and T3 are both like this, where ID can be repeated:

ID	UID
0	00
1	01
1	02
1	03

The expected output is T1 should be:

ID	COLUMN1
0	quotient of count of records in t2 and t3 where id is 0
1	quotient of count of records in t2 and t3 where id is 1

CodePudding user response：

Maybe this would work?

Update T1 
Set COLUMN1 = 1.0 * (select count(*) from T2 where T2.ID = T1.ID) / 
(select count(*) from T3 where T3.ID = T1.ID)

Multiplying by 1.0 to force the result to be a float.

CodePudding user response：

Dont know what data to mimic on T3 table and as per question mentioned it would be like T2 (assumed same ) and build query. Take a look

WITH t1 AS (
    SELECT
        *
    FROM
    VALUES
        (0, ''),(1, '') v(ID, COLUMN1)
),
t2 as (
    SELECT
        *
    FROM
    VALUES
        (0, 00),(1, 01),(1, 02),(1, 03) v(ID, UID)
),
t3 as (
    SELECT
        *
    FROM
    VALUES
        (0, 00),(1, 01),(1, 02) v(ID, UID)
),
t1_t2_count as (
    select
        count(*) as cnt
    from
        T1,
        T2
    where
        T2.ID = T1.ID
),
t1_t3_count as (
    select
        count(*) as cnt
    from
        T1,
        T3
    where
        T3.ID = T1.ID
)
 Select
   t1_t2_count.cnt t1t2_cnt,
   t1_t3_count.cnt as t1t3_cnt,
   div0(t1t2_cnt,t1t3_cnt) result  
from
    t1_t2_count,t1_t3_count;