How to count specific value from multiple column in another table and update the results

Question

I have table1 as following

a	b	c	d	e	f
10	23	29	33	37	40
9	13	21	25	32	42
11	16	19	21	27	31
14	27	30	31	40	42
16	24	29	40	41	42
14	15	26	27	40	42
2	9	16	25	26	40
8	19	25	34	37	39
2	4	16	17	36	39
9	25	30	33	41	44
1	7	36	37	41	42
2	11	21	25	39	45
22	23	25	37	38	42
2	6	12	31	33	40
3	4	16	30	31	37

And table2 as following

numbs	result
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

I would like to update table2.result buy counting total matched numbs value from table1 column(a,b,c,d,e,f)

Have tried the below mentioned script but it's taking really long time to update fully so appreciate if somebody could provide me any alternative script which calculates faster.

UPDATE public.table2 AS t2 SET result = (select sum(
    (CASE WHEN t2.numbs=t1.a THEN 1 ELSE 0 END) +
    (CASE WHEN t2.numbs=t1.b THEN 1 ELSE 0 END) +
    (CASE WHEN t2.numbs=t1.c THEN 1 ELSE 0 END) +
    (CASE WHEN t2.numbs=t1.d THEN 1 ELSE 0 END) +
    (CASE WHEN t2.numbs=t1.e THEN 1 ELSE 0 END) +
    (CASE WHEN t2.numbs=t1.f THEN 1 ELSE 0 END) )     
                FROM public.table1 AS t1 )

This is really a job for a programming language, where it would be easy. SQL does not provide for this kind of operation. — Tim Roberts
– Tim Roberts, Commented Jan 25, 2022 at 19:01

forpas · Accepted Answer · 2022-01-25 19:22:29Z

3

It looks like there are no duplicate numbers in each row of table1(something like lottery numbers).

If my assumption is correct, you can simplify your code:

UPDATE table2 AS t2
SET result = (
  SELECT COUNT(*) 
  FROM table1 AS t1 
  WHERE t2.numbs IN (t1.a, t1.b, t1.c, t1.d, t1.e, t1.f)
);

See the demo.

answered Jan 25, 2022 at 19:22

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mike Organek Over a year ago

In case there are dups, you can pivot columns to rows using jsonb functions. Modification to your DB<>Fiddle here: dbfiddle.uk/…

Gulya Over a year ago

@forpas it's working perfectly but still taking a lot of time to update 2mln rows of data, so is there any alternative script which calculates faster plz

Gulya Over a year ago

@Mike Organek the following error is occurring; ERROR: cannot cast jsonb string to type integer SQL state: 22023 FYI plz, there are many other columns in table1 and maybe that's the reason thre is an error

Mike Organek Over a year ago

@Gulya Yes. Extra columns would cause this problem. You did not mention them in your question.

Tim Roberts · Accepted Answer · 2022-01-25 19:18:42Z

OK, so it can be done. The fact that this is so ugly is an indication that your database is not properly designed. If the numbers can be handled independently, then they should be in separate rows.

DROP TABLE numbers;
CREATE TABLE numbers (
    a int,
    b int,
    c int,
    d int,
    e int,
    f int
);

INSERT INTO numbers  VALUES
(10, 23, 29, 33, 37, 40),
(9, 13, 21, 25, 32, 42),
(11, 16, 19, 21, 27, 31),
(14, 27, 30, 31, 40, 42),
(16, 24, 29, 40, 41, 42),
(14, 15, 26, 27, 40, 42),
(2, 9, 16, 25, 26, 40),
(8, 19, 25, 34, 37, 39),
(2, 4, 16, 17, 36, 39),
(9, 25, 30, 33, 41, 44),
(1, 7, 36, 37, 41, 42),
(2, 11, 21, 25, 39, 45),
(22, 23, 25, 37, 38, 42),
(2, 6, 12, 31, 33, 40),
(3, 4, 16, 30, 31, 37)
;
DROP TABLE table2;
CREATE TABLE table2 (
    numbs int,
    result int
);

INSERT INTO table2 VALUES
(1,0), (2,0), (3,0), (4,0), (5,0), (6,0), (7,0), (8,0), 
(9,0), (10,0), (11,0), (12,0), (13,0), (14,0), (15,0),
(16,0), (17,0), (17,0), (18,0), (19,0), (20,0), (21,0),
(22,0), (23,0), (24,0);

UPDATE table2 SET result=0;


UPDATE table2 SET result = result + n.cnt
    FROM (SELECT a,count(*) cnt FROM numbers GROUP BY a) n
    WHERE table2.numbs = n.a;
UPDATE table2 SET result = result + n.cnt
    FROM (SELECT b,count(*) cnt FROM numbers GROUP BY b) n
    WHERE table2.numbs = n.b;
UPDATE table2 SET result = result + n.cnt
    FROM (SELECT c,count(*) cnt FROM numbers GROUP BY c) n
    WHERE table2.numbs = n.c;
UPDATE table2 SET result = result + n.cnt
    FROM (SELECT d,count(*) cnt FROM numbers GROUP BY d) n
    WHERE table2.numbs = n.d;
UPDATE table2 SET result = result + n.cnt
    FROM (SELECT e,count(*) cnt FROM numbers GROUP BY e) n
    WHERE table2.numbs = n.e;
UPDATE table2 SET result = result + n.cnt
    FROM (SELECT f,count(*) cnt FROM numbers GROUP BY f) n
    WHERE table2.numbs = n.f;

Output:

sqlite> .mode box               
sqlite> select * from table2;   
┌───────┬────────┐              
│ numbs │ result │              
├───────┼────────┤              
│ 1     │ 1      │              
│ 2     │ 4      │              
│ 3     │ 1      │              
│ 4     │ 2      │              
│ 5     │ 0      │              
│ 6     │ 1      │              
│ 7     │ 1      │              
│ 8     │ 1      │              
│ 9     │ 3      │              
│ 10    │ 1      │              
│ 11    │ 2      │              
│ 12    │ 1      │              
│ 13    │ 1      │              
│ 14    │ 2      │              
│ 15    │ 1      │              
│ 16    │ 5      │              
│ 17    │ 1      │              
│ 17    │ 1      │              
│ 18    │ 0      │              
│ 19    │ 2      │              
│ 20    │ 0      │              
│ 21    │ 3      │              
│ 22    │ 1      │              
│ 23    │ 2      │              
│ 24    │ 1      │              
└───────┴────────┘              
sqlite>

eshirvana · Accepted Answer · 2022-01-25 19:25:43Z

1

here is one way :

update table2 
set result = (
  select sum(case when numbs in (a,b,c,d,e,f) then 1 else 0 end)
  from numbers
);

db<>fiddle here

answered Jan 25, 2022 at 19:25

eshirvana

24.7k3 gold badges28 silver badges43 bronze badges

2 Comments

Gulya Over a year ago

the script is working perfectly super fast but the results for the entire 1.2mln rows of data is same results like 7 on the entire column

eshirvana Over a year ago

@Gulya , I can' tell what's happeneing on your side , but as you can see in fiddle , its working ok

Collectives™ on Stack Overflow

How to count specific value from multiple column in another table and update the results

3 Answers 3

4 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

a	b	c	d	e	f
10	23	29	33	37	40
9	13	21	25	32	42
11	16	19	21	27	31
14	27	30	31	40	42
16	24	29	40	41	42
14	15	26	27	40	42
2	9	16	25	26	40
8	19	25	34	37	39
2	4	16	17	36	39
9	25	30	33	41	44
1	7	36	37	41	42
2	11	21	25	39	45
22	23	25	37	38	42
2	6	12	31	33	40
3	4	16	30	31	37

a	b	c	d	e	f
10	23	29	33	37	40
9	13	21	25	32	42
11	16	19	21	27	31
14	27	30	31	40	42
16	24	29	40	41	42
14	15	26	27	40	42
2	9	16	25	26	40
8	19	25	34	37	39
2	4	16	17	36	39
9	25	30	33	41	44
1	7	36	37	41	42
2	11	21	25	39	45
22	23	25	37	38	42
2	6	12	31	33	40
3	4	16	30	31	37

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related

a	b	c	d	e	f
10	23	29	33	37	40
9	13	21	25	32	42
11	16	19	21	27	31
14	27	30	31	40	42
16	24	29	40	41	42
14	15	26	27	40	42
2	9	16	25	26	40
8	19	25	34	37	39
2	4	16	17	36	39
9	25	30	33	41	44
1	7	36	37	41	42
2	11	21	25	39	45
22	23	25	37	38	42
2	6	12	31	33	40
3	4	16	30	31	37