homepage Welcome to WebmasterWorld Guest from 54.196.62.132
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Percentage of multiple numbers in single column (mysql)
Percentage of multiple numbers
somecat




msg:4573184
 11:08 pm on May 11, 2013 (gmt 0)

I could use a little help guys, can't seem to find an answer.

I have a column in a table with varying numbers ranging from 1 to 3.

I need to count how many rows I have for each number and then show a percentage for each.

I've got the totals for each but not sure how to proceed with percentages. Do I need a new query or do this with php after I get the totals? I would like to accomplish this in one query if its possible.

Here is what I have so far:

$query = "SELECT col, COUNT(col) FROM tbl WHERE id = '10' GROUP BY col DESC";



The result is:

number 3 found 12 times
number 2 found 5 times
number 1 found 35 times




Thank you in advance for your help!

 

dmorison




msg:4574425
 2:54 pm on May 15, 2013 (gmt 0)

You can do it in a single query using a subquery SELECT clause to get the non-GROUP'ed total...

Have a go with

$query = "SELECT (100/(SELECT COUNT(*) FROM tbl WHERE id = '10')*COUNT(col)) AS percentage,col, COUNT(col) FROM tbl WHERE id = '10' GROUP BY col DESC";

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved