CODEWITHSUNDEEP
sqlsql-server
BuddyCool
BuddyCool

Reputation: 91

Use of count and sum together in SQL

I have the following data from a view in ms-sql. I wanted to group the results by LossTypeExt and create a new column Paid Severity as following:

Formula to calculate the PaidSeverity: 

  paid serverity = sum(totalPiadamount)/distinct count of claims

So, my final result should LossTypeExt and PaidSeverity($amt). Any thoughts or help will be much appreciated. Thanks!

Sample Data/Table: 

         ClaimKey   ClaimNum    ClaimOpenDate   TransactionDate TotalLossPaid       LossTypeExt         

         2143672    2143672     2016-11-11      2016-11-16          0.00            Water   
         2143673    2143673     2016-11-15      2016-11-16          5266.00         Wind   
         2143674    2143674     2016-11-15      2016-11-16          151.55          Hail   
         2143675    2143675     2016-12-12      2016-11-16          2656.00         Water   
         2143676    2143676     2016-11-15      2016-11-16          5652.00         AOPD   
         2143677    2143677     2016-11-15      2017-11-16          4545.00         Liability   
         2143678    2143678     2018-18-16      2016-11-16          124.00          Liability 
         2143679    2143679     2018-05-15      2016-11-16          4541.00         AOPD
         2143680    2143680     2017-11-18      2016-11-16          966.00          Fire

Expected Data should be like this: 

         LossTypeExt    PaidSeverity

         Water          $amt
         Wind           $amt
         Fire           $amt
         Hail           $amt    
         AOPD           $amt
         Liability      $amt

Query: 

  select LossTypeExt,
  sum(TotalLossPaid)/claims_cnt as PaidServerity 
 from dbo.vw_Financial_Transactions
cross join
 (select count(distinct claimNum) claims_cnt  from dbo.vw_Financial_Transactions ) as cnt
   group by LossTypeExt

Upvotes: 0

Views: 61

Answers (2)

Cetin Basoz
Cetin Basoz

Reputation: 23797

No need for a subquery for the count:

select LossTypeExt, 
sum(TotalLossPaid)*1.0/ count(distinct claimKey) 
from myTable
group by LossTypeExt;

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can do :

select LossTypeExt, 
       sum(TotalLossPaid) / claims_cnt as paid serverity 
from table cross join
     (select count(distinct claims) claims_cnt from table) as cnt
group by LossTypeExt;

Upvotes: 1

Related Questions