Percentages

[glendango]

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 April 11, 2018 by glendango

[requinix]

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.

[glendango]

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

[glendango]

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 April 11, 2018 by glendango

[requinix]

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.

[Barand]

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

[glendango]

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