count record and put into the field RSS

• • Reply
  

  melvintcs

  Member

  182 Points

  238 Posts

  count record and put into the field

  Nov 28, 2012 07:47 AM|LINK

  I have this table

  ID         gender       companyName
  1             M               Company A
  2             M               Company A
  3             F                Company A
  4             F                Company B

  I want this result:

  CompanyName            M         MPercent    F     FPercent
  Company A                    2              66           1         33
  Company B                    0               0            1        100

  *MPercent for Company A is 2/3 = 66%, FPercent is 1/3 = 33%.
  *MPercent for Company B is 0/1 = 0%, FPercent is 1/1 = 100%.

  Possible to get the result by using mysql query only? 

• • Reply
  

  alankarp

  Contributor

  2042 Points

  345 Posts

  Re: count record and put into the field

  Nov 28, 2012 08:10 AM|LINK

  Hi,

  Please check below query

  select  companyName,
  sum(case when gender ='M' Then 1 else 0 end CASE) as M,
  (sum(case when gender ='M' Then 1 else 0 end case)/count(gender)) * 100 as  MPercent,
  sum(case when gender ='F' Then 1 else 0 end case) as F,
  (sum(case when gender ='F' Then 1 else 0 end case)/count(gender)) * 100 as  FPercent,
  from table group by companyName,gender

  Please note : not tested on query Analyzer

  Thanks

  
  
  

  Alankar Patil
  
  Profile
• • Reply
  

  melvintcs

  Member

  182 Points

  238 Posts

  Re: count record and put into the field

  Nov 28, 2012 08:26 AM|LINK

  thx for the reply 

  ur code is working, but there is one small error with the COUNT(gender), ur code above is included all the gender result which included Company B. 

  we need to filter the gender by CompanyName first. :)

• • Reply
  

  alankarp

  Contributor

  2042 Points

  345 Posts

  Re: count record and put into the field

  Nov 28, 2012 08:55 AM|LINK

  I am using companyName first in gourp by clause and then gender.. I think myslq should group companies first and then gender ...not sure why it is not working.. i don't have environment to test any query so not able to test it

  Alankar Patil
  
  Profile
• • Reply
  

  melvintcs

  Member

  182 Points

  238 Posts

  Re: count record and put into the field

  Nov 29, 2012 12:42 AM|LINK

  Hi, i modified your code, and it's working now, thank you :)

  select  companyName,
  sum(case when gender ='M' Then 1 else 0 end) as M,
  (sum(case when gender ='M' Then 1 else 0 end)/count(gender)) * 100 as  MPercent,
  sum(case when gender ='F' Then 1 else 0 end) as F,
  (sum(case when gender ='F' Then 1 else 0 end)/count(gender)) * 100 as  FPercent,
  from table group by companyName

  • Edited by melvintcs on Nov 29, 2012 12:43 AM
  • Edited by melvintcs on Nov 29, 2012 12:44 AM
  • Edited by melvintcs on Nov 29, 2012 12:44 AM
  • Edited by melvintcs on Nov 29, 2012 05:19 AM
  • Marked as answer by melvintcs on Nov 29, 2012 05:19 AM