CODEWITHSUNDEEP
mysqlsql
Petros Nicolaou
Petros Nicolaou

Reputation: 117

For every ID in the table, count how many times that ID appears in another Column. MySQL

Table: MyTable

[My Table1

Here is the result I desire:

For every ID in the table, count how many times that ID appears in the column Parent_ID. Create a custom column AS Instances to place the result.

My desired Result

My Result

I imagine to get the above result with something not more complicated than a working version of the following query:

SELECT ID, Parent_ID, COUNT( Parent_ID = ID ) AS Instances FROM MyTable

Upvotes: 1

Views: 2351

Answers (2)

The Impaler
The Impaler

Reputation: 48770

You can use a scalar subquery to compute the extra column. For example:

select
  id,
  parent_id,
  (
    select count(*) from my_table b where b.parent_id = a.id
  ) as instances
from my_table a

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269773

A correlated subquery is the simplest solution:

select t.*,
       (select count(*) from mytable t2 where t2.parent_id = t.id) as instances
from mytable t;

Upvotes: 0

Related Questions