Question 175438 in Mysql Question posted in
The official MySQL documentation can be found here.

What percentage of a particular String appears in a column in a MySQL table?

I have a very simple table that contains Status_var and description. You can see example here:
http://sqlfiddle.com/#!18/3e0d1/5

I need to be able to count how many ‘Pass’ string occurences are in Status_var column and calculate percentage. Example table below:
enter image description here

In the above example, we have 2 "Pass" and 1 "Fail" occurences. The result should be 66%.

I have found a very simmillar solution to this problem in this post:
https://dba.stackexchange.com/questions/311416/mysql-getting-the-percentage-of-an-occurrence

And I tried to implement it in sqlfiddle:
http://sqlfiddle.com/#!18/3e0d1/22

But it does not work
enter image description here

Tags:

3

Answers


  1. 0

    Pretty simple approach is adding another SELECT around your query specifying the appropriate condition (you’ll have to give the calculated column a name for); this might look like:

    SELECT * FROM ( --                                              vvvvvvvvvvvvv (!)

  select Status_var, count(Status_var)*100/sum(count(*)) over() AS percentage
  from ForgeRock
  group by Status_var

) sp WHERE status_var = 'Pass'

    EDIT: Your second query you adopted from the other answer actually seems to be fine – on mySQL! See sqlfiddle – but you need to set mySQL as database, you tried on MS SQL which doesn’t seem to accept comparisons at this place; it seems to interpret the equal-sign as assignment, as the query

    SELECT status_var, Status_var = 'Pass' FROM ForgeRock

    reveals…

    Login or Signup to reply.
  2. 0

    Here is my solution for your problem:

    SELECT * FROM ( 
  select Status_var, count(Status_var)*100/sum(count(*)) over() AS percentage
  from ForgeRock
  group by Status_var

) sp WHERE status_var = 'Pass'

    For your mentioned link i would go with:

    SELECT SUM(CASE WHEN Status_var='Pass' THEN 1 ELSE 0 END) * 100 / COUNT(*) 
FROM ForgeRock;

    To get the result 66.

    Login or Signup to reply.
  3. 0

    This could be more efficient, since will count only the ones with Status_var = 'Pass' :

    select *
from (
  select Status_var, count(case when Status_var = 'Pass' then 1 end )*100/ sum(count(*)) over() AS PERCENT
  from ForgeRock
  group by Status_var
) as s
where Status_var = 'Pass'

    Also an index On Status_var will be helpfull

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search