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