I’m new to SQL and have a basic question. I’ve two columns as Name and Salary and their values
| Name | Salary |
|---|---|
| Amit | 1000 |
| Harsh | 2000 |
| Priya | 5000 |
| Priya | 4000 |
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:
| Name | Count | Rate |
|---|---|---|
| Amit | 1 | 0.25 |
| Harsh | 1 | 0.25 |
| Priya | 2 | 0.5 |
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
Advertisement
Answer
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.