Jump to content
glendango

Percentages

Recommended Posts

Posted (edited)

Hi. 

Looking to get a % from my table(firsts) to give user stats..

 

I have :

 

Date_made                   Status

 

Date 1                             0

Date 2                             0

Date 3                             1

Date 4                             0

 

 

The answer will be 25%  because their is 1 instance of '1' divided by 4 dates = 25%   ( i know you can do sums so sorry for basic explain. )  

 

i have tried below query  and get an error but it best explains what iam trying to do:

 

SELECT count(status) ,count(date_made) , status / date_made   *100  from firsts where status = '1' 

 

i ve tried for a few hours but i just get back answer of 100 or 7x100 = 700 etc.. 

sorry i get back 700 in my real app becuase their are 7 instances of '1'

Edited by glendango

Share this post


Link to post
Share on other sites

I would think a simple

SELECT AVG(Status) FROM firsts
should be enough to get you 0.25. You'll probably have to WHERE the rows you want to consider, which is not just the Status=1 rows because you also want to factor in the Status=0 rows.
  • Like 1

Share this post


Link to post
Share on other sites

what the.. ?    how is that even working without telling mysql to count only the rows with '1' in them ? 

Share this post


Link to post
Share on other sites
Posted (edited)

i get it...its adding the values up and dividing them...   what if i created a new status for the user which i called '2'.

it wouldnt  work then would it.   

 

i.e. in my case

0 = no sale

1= sale

2 = completed sale 

 

this works i think

 

SELECT AVG(Status=1) FROM firsts where usr_id='1'

Edited by glendango

Share this post


Link to post
Share on other sites

Actually in that case you would use a WHERE on the Status, but it would restrict to 0s and 1s. Assuming you wanted to ignore 2s.

SELECT AVG(Status) FROM firsts WHERE Status IN (0, 1)
If the values were 0,0,1,2 (order doesn't matter) then the average would be 0+0+1 / 3 = 0.33 because the 2 would not be included.

 

If you didn't want to ignore the 2s then... well, it depends what you would want to do with them. If they were to count as 0s then what you have now is fine.

With the same values as above, counting 2s as 0s would have the average be 0+0+1+0 / 4 = 0.25. Note that this is different from the other average because whether you include the rows of 2s matters.

  • Like 1

Share this post


Link to post
Share on other sites

IMHO it should work without the WHERE. I set a test table with 10 records (to keep the sums easy)

SELECT * FROM firsts;
+------+--------+
|  id  | status |
+------+--------+
|    1 |      0 |
|    2 |      0 |
|    3 |      1 |
|    4 |      0 |
|    5 |      2 |
|    6 |      1 |
|    7 |      2 |
|    8 |      0 |
|    9 |      1 |
|   10 |      0 |
+------+--------+
10 rows in set (0.00 sec)

Then, to get the percentage of records with each status value

SELECT AVG(Status=2)*100 as `%_completed` 
     , AVG(Status=1)*100 as `%_sold`
     , AVG(Status=0)*100 as `%_no_sale`
FROM firsts;
+-------------+---------+-----------+
| %_completed | %_sold  | %_no_sale |
+-------------+---------+-----------+
|     20.0000 | 30.0000 |   50.0000 |
+-------------+---------+-----------+

"status=1" evaluates to either 1 or 0, so the AVG(status=1) effectively gives a count of those values / total record count

  • Like 1

Share this post


Link to post
Share on other sites

Thanks as always for your time and answers. I will use that table for users to see the breakdown.. cool 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.