CODEWITHSUNDEEP
mysql
jonmrich
jonmrich

Reputation: 4323

mySQL query giving inaccurate results

This is my current query:

select baseball , const.cnt/count(*) as my_percent 
from full_db3 inner join
 (select count(*) as cnt from full_db3 where gender = 'f') const
group by baseball
order by my_percent desc limit 10;

This yields inaccurate results. The possible values for baseball are TRUE and FALSE, which appear in my result's baseball column. However, the values for my_percent are all off. Not even close.

If I run this query (different GROUP BY), I get the correct value in my_percent for FALSE, but TRUE isn't in the result.

select baseball , const.cnt/count(*) as my_percent 
from full_db3 inner join
 (select count(*) as cnt from full_db3 where gender = 'f') const
group by const.cnt
order by my_percent desc limit 10;

What am I missing here?

Upvotes: 0

Views: 53

Answers (2)

pala_
pala_

Reputation: 9010

How about something simpler:

select baseball , 
      (sum(case when gender = 'f' then 1 else 0 end) / count(*)) * 100 as pct
FROM full_db3
group by baseball;

This query gives percentage of females that are players / non players;

select gender, 
   (sum(baseball) / count(baseball)) * 100 as players, 
   (1 - (sum(baseball) / count(baseball))) * 100 as non_players
    from full_db3
    where gender = 'f'
    ;

And the last one, that has the true / false in the rows, as finally determined as the requirement:

 select baseball, 
    (count(*) / 
       (select count(gender) from full_db3 where gender = 'f')) * 100 as pct
 from full_db3 
 where gender = 'f'
 group by baseball;

fiddle: http://sqlfiddle.com/#!9/15866/6

Upvotes: 1

O. Jones
O. Jones

Reputation: 108641

It looks like you're trying to show percentages of people who play baseball.

Don't you want

SELECT baseball, gender, (100.0 * COUNT(*) / const.cnt) AS my_percent
  FROM full_db3
  JOIN (SELECT COUNT(*) AS cnt FROM full_db3) CONST
 GROUP BY baseball, gender

You might also want.

SELECT baseball, gender, (100.0 * COUNT(*) / const.cnt) AS my_percent
  FROM full_db3
  JOIN (
     SELECT COUNT(*) AS cnt, gender
       FROM full_db3
      GROUP BY gender
       ) CONST ON full_db3.gender = CONST.gender
 GROUP BY baseball, gender

If you want to know what percentage of 'm' and 'f' genders separately have the baseball attribute.

The 100.0 * turns your fraction into a percentage

Your LIMIT 10 makes no sense in this case because your aggregate query doesn't contain ten different categories.

Upvotes: 0

Related Questions