CODEWITHSUNDEEP
sqlt-sql
user2919277
user2919277

Reputation: 246

count and divide two columns of the same table in sql

I need to get the count of records which have Id as 11 and 12 separate. Then i have to group them by period (which is 1,2,3,4,5) and divide each ID with their corresponding group. i.e (count of ID=11 with period 1) / (count of ID=12 with period 1)

I have tried this. But this just gives me a count of each. How can i divide them in the same table.

Example : 

    SELECT COUNT(pNum), SK_MetricDatePeriod , SK_MetricID
FROM
(
   SELECT SK_PatientID as pNum , SK_MetricDatePeriod ,SK_MetricID FROM [IntegratedCare].[report].[MetricValues] WHERE SK_MetricID = 11
   UNION ALL 
   SELECT SK_PatientID ,SK_MetricDatePeriod , SK_MetricID FROM [IntegratedCare].[report].[MetricValues] WHERE SK_MetricID = 12
) t
WHERE pNum IS NOT NULL 
GROUP BY SK_MetricID
     ,SK_MetricDatePeriod
ORDER BY SK_MetricID,SK_MetricDatePeriod;

The result is :

Count  MetricPeriod  MetricID
10199   1   11
10075   2   11
9991    3   11
9891    4   11
8952    5   11
12298   1   12
12130   2   12
12058   3   12
11943   4   12
10860   5   12

How can count and divide. on the above query.

Result i am looking like:

 DividedResult MetricPeriod  
    10199   1   
    10075   2   
    9991    3   
    9891    4   
    8952    5

DivideResult is got by dividing (Countvalue where period =1 and metric id =11) / (Countvalue where period =1 and metric id =12)

Upvotes: 0

Views: 3298

Answers (3)

Raga Mounika Chimata
Raga Mounika Chimata

Reputation: 1

Here i used case statement , it worked for me

select CompanyCode  ,'Commission Pec',  Year
,sum(case  when CommissionType='Commission Recevied' then JAN else 0 end)/sum(case when CommissionType='Net Payments from WM' and isnull(JAN,0)<>0 then JAN else 1  end) 
 from Commission_Consolidate

 group by CompanyCode,Year

 end

Upvotes: 0

user3041160
user3041160

Reputation: 684

The Having count .. is to avoid a division by 0 

   SELECT count(SK_PatientID) as pNum
            , count(case when SK_MetricID = 11 then 0 end) / count(case when SK_MetricID = 12 then 0 end)
            , SK_MetricDatePeriod
   FROM [MetricValues] 
   WHERE SK_MetricID In (11, 12)
   GROUP BY SK_MetricDatePeriod
   Having count(case when SK_MetricID = 12 then 0 end) > 0

Upvotes: 1

BasicHorizon
BasicHorizon

Reputation: 171

Not a full answer but it wouldn't let me comment.

Wouldn't it be easier to select your original query into a working table and then Divide pNum where MetricPeriod = MetricPeriod

If you are just looking for an average across every MetricID you could just sum you pnum of each occurence of period and then divid by the total number of periods? Just throwing yuo some ideas hope this helps.

Upvotes: 0

Related Questions