CODEWITHSUNDEEP
sql
Tacy Nathan
Tacy Nathan

Reputation: 475

MS SQL JOINing a Table to Itself

Col1 | Col2 |
10     NULL
20     NULL
30     NULL
20     NULL

I have a Table and I want to update each row of Col2 with the follwoing condition: Some scaler number X/(Count of the value from Col1). That is if X = 8 then I should have the follwoing values 8/1 , 8/2, 8/1, 8,2 and so on..

I am doing the following:

SELECT COUNT(*)
FROM (
    SELECT [Table].Col1
    FROM [Table]
    INNER JOIN [Table] T ON [Table].Id = T.Id
    WHERE [Table].Col1 = T.Col1
    )

Can you point out my mistake please?

Upvotes: 0

Views: 241

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You need something like this:

SELECT t1.Col1, 8 / t2.cnt
FROM [Table] t1 
INNER JOIN (
   SELECT Col1, COUNT(*) AS cnt
   From [Table]
   GROUP BY Col1
) t2 ON t1.Col1 = t2.Col2

The above query uses a derived table that contains the count per Col1 value. Using this count we can do the math required for each row of the original table.

Upvotes: 2

Related Questions