By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,920 Members | 1,767 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,920 IT Pros & Developers. It's quick & easy.

Calculate percentage for each row

P: 4
Hi,

Would like to get percentage for generated column. Any idea on how to do that??

Following SQL statement

Expand|Select|Wrap|Line Numbers
  1. SELECT s.Selection_Desc, count(u.user_id)
  2. from tbl_system_selection s, tbl_user u, tbl_user_company c
  3. where s.selection_type = 'INDUSTRY'
  4. and u.User_Company_ID = c.Company_ID 
  5. and c.Company_Industry_ID = s.Selection_ID 
  6. and u.user_activated = '1'
  7. group by s.selection_id

gives the result of

======================================
Expand|Select|Wrap|Line Numbers
  1. Selection_desc  count(u.user_id)
  2.  
  3. Marketing         44
  4. Sales               54
  5. Finance           100
=======================================

i would like to get percentage for each row

for example Finance shd give 50% (100/(45+55+100))*100)

======================================
Expand|Select|Wrap|Line Numbers
  1. Selection_desc  count(u.user_id)  perc
  2.  
  3. Marketing         45                       22.5
  4. Sales               55                       27.5
  5. Finance           100                      50
=====================================
Nov 9 '07 #1
Share this Question
Share on Google+
3 Replies


Atli
Expert 5K+
P: 5,058
Hi.

This is something I would do using a external API, like PHP. It would be much simpler than doing it via the MySQL query, if it is even possible.

If this has to be done via MySQL I would suggest using a stored procedure.
Nov 9 '07 #2

P: 4
is there any way, for example, like nested query?
Nov 12 '07 #3

amitpatel66
Expert 100+
P: 2,367
is there any way, for example, like nested query?
Expand|Select|Wrap|Line Numbers
  1. select mgr, COUNT(mgr),((count(mgr)/(SELECT COUNT(*) from emp)) * 100) from emp where mgr IS NOT NULL group by mgr
  2.  
The above query gives me the % for the no of employees unser a particular manager. try applying this logic to your query for finding the %

I hope this helps!!
Nov 12 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.