CODEWITHSUNDEEP
mysqlsqlprobability
yihang hwang
yihang hwang

Reputation: 419

How to count the probability and update the calculation value to the original table using MySQL?

Below is an example how can I use a simple MySQL query that I can do from step 1 ->step 4

step 1: original table

object prob
dog
dog
cat
cat
book
book

step 2: count the object

dog  2
cat  2
book 2

step 3: calculate the probability

dog  2/(2+2+2)
cat  2/(2+2+2)
book 2/(2+2+2)

step 4: return the probability value to original

Object prob
dog     2/6
dog     2/6
cat     2/6
cat     2/6
book    2/6
book    2/6

Upvotes: 0

Views: 481

Answers (1)

ssn
ssn

Reputation: 509

What exactly are you asking?

You want to know how to Count each item and insert the probability of each?

Update:

This piece of code will do what you are asking. Just replace #temp with your own table.

    DECLARE @object nvarchar(max)

    DECLARE object_cursor CURSOR FOR 
        SELECT object
        FROM #temp

    OPEN object_cursor

    FETCH NEXT FROM object_cursor
    INTO @object

    WHILE @@FETCH_STATUS = 0
    BEGIN

        UPDATE #temp
        SET prob = (
                    SELECT
                        COUNT(object)
                    FROM #temp
                    WHERE object = @object          
        )

        FETCH NEXT FROM object_cursor
        INTO @object

    END
    CLOSE object_cursor
    DEALLOCATE object_cursor

    UPDATE #temp
    SET prob = prob / (select SUM(prob) from #temp)

Upvotes: 1

Related Questions