# Percentages

## Recommended Posts

Hi.

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

I have :

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 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.

##### Share on other sites

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

##### 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'

Edited by glendango

##### 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.

##### 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

##### Share on other sites

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

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

×   Pasted as rich text.   Restore formatting

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×

×

• #### Activity

• Chat
×
• Create New...