sql server - Is it possible to iterate a calculation across distinct values in a column? -


i want iterate calculation takes counts of people fit particular criteria , calculates percentages based on counts across distinct regions.

my code:

use database1; go   declare @shouldregister float declare @registered float   set @shouldregister = (select count(*) dbo.table                  field1 in..                  , field2 in..                   , field3 in..                 ...                 )  set @registered = (select count(*) dbo.table                 field1 in..                 , field2 in..                 , field3 in..                 ...                 )  select        @shouldregister shouldregister      , @registered registered     , cast((@registered/nullif(@shouldregister, 0))*100 decimal(12,8)) percentmet     , cast(100*2.33*(sqrt(@registered/nullif(@shouldregister, 0) * (1-(@registered/nullif(@shouldregister, 0)))/nullif(@shouldregister, 0))) decimal(12,8)) + cast((@registered/nullif(@shouldregister, 0))*100 decimal(12,8)) adjpercentmet 

the code returns this:

shouldregister  registered  percentmet      adjpercentmet 223587          565         0.25269805      0.27743717 

each person has region assigned in "region" column. code above calculates across regions. see is:

shouldregister  registered  percentmet      adjpercentmet   region 223             50          0.12345678      0.12345678      region1 456             100         0.12345678      0.12345678      region2 789             456         0.12345678      0.12345678      region3 

my brain wants do: "for region in regions, (code)", don't think sql works way.

try way :-

set nocount on;  select   t.shouldregistered         ,t.registered         ,t.region         ----- isnull(,0) cause of divide 0 error if null         ,cast((t.registered / (case when isnull(t.shouldregistered,0) = 0 1 else t.shouldregistered) * 100) decimal(12,8)) percentmet         ,[adjpercentmet condition] adjpercentmet    (             select   tb.region                     ,sum(case when [tb.field1.....condition] , [tb.field2.....condition] , [tb.field3....condition] 1 else 0 end) shouldregistered                     ,sum(case when [tb.field1.....condition] , [tb.field2.....condition] , [tb.field3....condition] 1 else 0 end) registered                dbo.table tb (nolock)             group tb.region         ) t group t.shouldregistered         ,t.registered         ,t.region order t.region 

Comments

Popular posts from this blog

twig - Using Twigbridge in a Laravel 5.1 Package -

jdbc - Not able to establish database connection in eclipse -

Kivy: Swiping (Carousel & ScreenManager) -