I have two sql tables t1 and t2, I want to fill a column in t1 with the number of unique values in a column in t2.

Example: t1 has two column a and count

   a   count
1  1  
2  1  
3  2

Example: t2 has one column b

   b 
1  3  
2  3  
3  2
4  5
5  5

t2 has 3 unique values in column b, I want to have t1 like this, column count to be filled by the number of unique values in t2.b

   a  count
1  1  3
2  1  3
3  2  3

CodePudding user response：

Not sure if I get your question wrong. I guess you want to find out all distinct values in t2 and their counts. What you need is GROUP BY and INSERT INTO ... SELECT

CREATE TABLE t1
(
   a INTEGER,
   count INTEGER
);


CREATE TABLE t2
(
   b INTEGER
);

INSERT INTO t2 VALUES(1);
INSERT INTO t2 VALUES(1);
INSERT INTO t2 VALUES(1);
INSERT INTO t2 VALUES(2);
INSERT INTO t2 VALUES(2);
INSERT INTO t2 VALUES(3);

INSERT INTO t1
    (a, count)
SELECT
    b,
    count(b)
FROM
    t2
GROUP by
    b;
    
SELECT * FROM t1;

CodePudding user response：

create your tables

declare @t1 table(
   FIELD1 INTEGER  NOT NULL PRIMARY KEY 
  ,a      INTEGER  NOT NULL 
);
INSERT INTO @t1(FIELD1,a) VALUES (1,1);
INSERT INTO @t1(FIELD1,a) VALUES (2,1);
INSERT INTO @t1(FIELD1,a) VALUES (3,2);

declare @t2 table(
  b      INTEGER  NOT NULL
);
INSERT INTO @t2(b) VALUES (3);
INSERT INTO @t2(b) VALUES (3);
INSERT INTO @t2(b) VALUES (2);
INSERT INTO @t2(b) VALUES (5);
INSERT INTO @t2(b) VALUES (5);

your select

select FIELD1,a

,(select count(distinct b) from @t2) as count1 

from @t1