Percentage with group by

• Nilssond

  Hall of Fame

  Points: 3285

  More actions

  March 17, 2005 at 5:48 pm

  #164878

  I have a table which contains the fields:

  Plancode

  Startdate

  MemberNumber

  I need to have a query which will display the counts of each plancode, the

  percentage of each plan relative to the whole table ( all the plancodes) , and the plancode

  in a single result set.  Any help out there?

• ron k

  SSCertifiable

  Points: 6103

  More actions

  March 17, 2005 at 6:15 pm

  #547695

  Select count(*),

         ((Select count(*) From YourTable Where PlanCode = YT.plancode) /

          (Select count(*) From YourTable) * 100 ) as [Percent],

   PlanCode

  From YourTable YT

  Group by PlanCode

  There's gotta be a better way

   

   

• wz700

  SSCertifiable

  Points: 7133

  More actions

  March 17, 2005 at 6:19 pm

  #547696

  declare @allplancode int

  select @allplancode = count(*) from plancode

  select plancode, count(*) as plancodecnt , 100.0 * count(*) / @allplancode as percent

   from thetable

   group by plancode