Jump to content

Percentages


glendango

Recommended Posts

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'

Link to comment
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.
Link to comment
Share on other sites

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'

Link to comment
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.

Link to comment
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

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

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.