count the instances of value in sub-query, update table

Question

I am trying to count the number of times a value (mytype) appears within a distinct id value, and update my table with this count (idsubtotal) for each row. The table I have:

id   | mytype | idsubtotal
-----+--------+-----------
 44    red  
101    red     
101    red
101    blue
101    yellow
494    red
494    blue
494    blue
494    yellow
494    yellow

I need to calculate/update the idsubtotal column, so it is like:

id   | mytype | idsubtotal
-----+--------+-----------
 44    red      1
101    red      2    
101    red      2
101    blue     1
101    yellow   1
494    red      1
494    blue     2
494    blue     2
494    yellow   2
494    yellow   2

When I try this below, it is counting how many times the mytype value appears in the entire table, but I need to know how many times it appears within that sub-group of id values (e.g. How many times does "red" appear within id 101 rows, answer = 2).

SELECT id, mytype,
COUNT(*) OVER (PARTITION BY mytype) idsubtotal
FROM table_name

I know storing this subtotal in the table itself (versus calculating it live when needed) constitutes a bad data model for the table, but I need to do it this way in my case.

Also, my question is similar to this question but slightly different, and nothing I've tried to tweak using my very primitive understanding of SQL from the previous responses or other posts have worked. TIA for any ideas.

score 3 · Answer 1 · answered Aug 07 '12 at 23:12

3

UPDATE table_name a
SET idsubtotal=(  SELECT COUNT(1)
                  FROM table_name  b
                  WHERE a.id=b.id
                  AND a.mytype=b.mytype
                )

answered Aug 07 '12 at 23:12

Faruk AZAKLI

101
2

Just a note: This does not work in SQL Server. I know the question was for Oracle, just adding this in here. – vaindil Feb 17 '17 at 20:52

Razvan · Accepted Answer · 2012-08-07T19:58:47.727

1

When I try this below, it is counting how many times the mytype value appears in the entire table, but I need to know how many times it appears within that sub-group of id values (e.g. How many times does "red" appear within id 101 rows, answer = 2).

SELECT id, mytype, COUNT(*) 
FROM table_name
GROUP BY id, mytype

edited Aug 07 '12 at 19:58

answered Aug 07 '12 at 19:36

Razvan

9,925
6
38
51

One quick related question, because I am doing this on many records and it will take a long time to run, is there a way to, instead of just SELECTing the results, to have them update the idsubtotal column? Some combo of UPDATE and SELECT using your solution? – Tim West Aug 07 '12 at 20:13
selected answer here might help: http://stackoverflow.com/questions/6286452/mysql-bulk-insert-or-update – Razvan Aug 07 '12 at 20:27

count the instances of value in sub-query, update table

2 Answers2