Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pt315
Starting Member
3 Posts |
 Posted - 2010-11-18 : 05:56:10
|
| Hey,i'm very new to SQL (week 2 of an internship), so my problem is probably very easy to solve.Here my current table: taxonomy |org_cnt |+-----------------------------------------+--------+| Bacteria | 75 || Plants | 62 || Mammalia | 14 || Insects | 14 || Euglenozoa | 10 || Fungi | 8 |+-----------------------------------------+--------|and here is how i want it to be: taxonomy |org_cnt |percentage|+-----------------------------------------+--------+----------+| Bacteria | 75 | 0.409| Plants | 62 | 0.338| Mammalia | 14 | 0.076| Insects | 14 | 0.076| Euglenozoa | 10 | 0.054| Fungi | 8 | 0.043+-----------------------------------------+--------|so i want to add a column that shows the percentage. (Bacteria org_cnt / overall org_cnt etc)i've been trying everything i can think of and spent the last 3 hours looking for a way to do that, but i'm just too new at sql.i tried something like UPDATE testdb.countresult SET percentage = (SELECT (org_cnt)) / SUM(org_cnt)which i guess is totally wrong.i could really use your help on this |
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2010-11-18 : 06:23:31
|
| UPDATE testdb.countresult SET percentage = org_cnt /(select sum(org_cnt) from testdb.countresult) |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-11-18 : 07:31:41
|
| I would suggest casting org_cnt to float/decimal before doing the division, otherwise the results will likely be 0 (integer division)--Gail ShawSQL Server MVP |
|
|
|
pt315
Starting Member
3 Posts |
Posted - 2010-11-18 : 07:35:45
|
| when i do UPDATE testdb.countresult SET percentage = org_cnt /(select sum(org_cnt) from testdb.countresult)i keep getting the following error:ERROR 1093 (HY000): You can't specify target table 'countresult' for update in FROM clause.:(select sum(org_cnt) FROM testdb.countresult works fine though... |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2010-11-18 : 07:47:22
|
| this is mysql ? |
|
|
|
pt315
Starting Member
3 Posts |
Posted - 2010-11-18 : 08:04:59
|
quote:
Originally posted by sakets_2000
this is mysql ?
yes |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-11-18 : 09:00:44
|
| This is a Microsoft SQL Server site. If you're working with a mysql database, rather ask questions on a mysql forum. forums.mysql.com or dbforums.com--Gail ShawSQL Server MVP |
|
|
|
|
|
|
Problem with sum() and count(*)