I'm new to SQL and have a basic question. I've two columns as Name and Salary and their values

I want to create another column name count and rate.
For count - I want the count of names in the table
For Rate - I want to find the value of first row under count column / the total value of count column

formula For row 1 : 1/(4) = 0.25, and so on for every row.

Expected Output:

I've tried to use the following code with no success:

SELECT name, count(Name) as count, count/SUM(count) as rate
FROM table
group by name

CodePudding user response：

Considering testcount as the table's name. You can solve your problem with this query:

SELECT name, count(name) as count, 1.0*count(name)/(select count(*) from testcount) as rate FROM testcount group by name;

My test was:

I have this table (inside the database on my computer):

Table testcount
Anne    1000.0
Bob     2000.0
Bob     3000.0
Carly   2000.0

With the query above, the output was:

Anne    1   0.25
Bob     2   0.50
Carly   1   0.25

You need to multiply by 1.0 because the division is an integer one, and you need to "cast" to double precision. So, one way to do that (it's not the only one) is multiplying by 1.0.