SQL Set Value of a Column Using COUNT(*)

Question

I want to set a column to a specific value of 1/COUNT(*) in an SQL table. I am wondering how to do this.

To make it clearer, consider the following example. We have a table table1

TABLE1:
    A  |  B  |  C  
---------------------
    1  |  9  |  0
    2  |  7  |  0
    4  |  8  |  0
    3  |  6  |  0

I want to set the column C with value of 1/COUNT(*), in this case, is 0.25. So the result should be:

TABLE1:
    A  |  B  |  C  
---------------------
    1  |  9  |  0.25
    2  |  7  |  0.25
    4  |  8  |  0.25
    3  |  6  |  0.25

However, I tried the following code:

UPDATE TABLE
SET C = 1/COUNT(*)

It does not work and gives me an error:

Error: ERROR: aggregate functions are not allowed in UPDATE
Position: 21

How am I supposed to do this in SQL? Thank you!

Kaushik Nayak · Accepted Answer · 2018-12-13 17:46:16Z

4

If you want an update, use a from clause:

UPDATE TABLE
    SET C = 1.0 / c.cnt
    FROM (SELECT COUNT(*) as cnt FROM yourtable ) c;

There is no reason to have a separate column. You can easily calculate this on the fly:

select t.*,
       1.0 / count(*) over ()
from t;

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

answered Dec 13, 2018 at 16:49

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Derviş Kayımbaşıoğlu · Accepted Answer · 2018-12-13 19:49:59Z

1

The Answer:

UPDATE TABLE SET C = 1.0 / (SELECT COUNT(*) FROM TABLE)

30.9k4 gold badges55 silver badges79 bronze badges

answered Dec 13, 2018 at 17:14

Cebrayil Memmedov

112 bronze badges

please consider to add comment on your post. Try to answer these questions: How did you solve it? why this is the solution?

Sometimes coders are in a hurry, here he provided just the answer.