Jump to content


Photo

Percentages


  • Please log in to reply
6 replies to this topic

#1 glendango

glendango
  • Members
  • PipPipPip
  • Advanced Member
  • 71 posts
  • LocationEngland

Posted 11 April 2018 - 10:52 AM

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, 11 April 2018 - 10:59 AM.


#2 requinix

requinix
  • Administrators
  • Impoverished Administrator
  • 9,817 posts
  • LocationWA

Posted 11 April 2018 - 11:52 AM

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.
The Reimann Zeta Function Trolley Problem | If you want to escape with me, come take my hand...

#3 glendango

glendango
  • Members
  • PipPipPip
  • Advanced Member
  • 71 posts
  • LocationEngland

Posted 11 April 2018 - 12:09 PM

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



#4 glendango

glendango
  • Members
  • PipPipPip
  • Advanced Member
  • 71 posts
  • LocationEngland

Posted 11 April 2018 - 12:38 PM

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, 11 April 2018 - 12:42 PM.


#5 requinix

requinix
  • Administrators
  • Impoverished Administrator
  • 9,817 posts
  • LocationWA

Posted 11 April 2018 - 01:44 PM

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.
The Reimann Zeta Function Trolley Problem | If you want to escape with me, come take my hand...

#6 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,110 posts

Posted 11 April 2018 - 02:49 PM

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


If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#7 glendango

glendango
  • Members
  • PipPipPip
  • Advanced Member
  • 71 posts
  • LocationEngland

Posted 11 April 2018 - 05:20 PM

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






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users