CODEWITHSUNDEEP
sqlaveragesql-server-2014
BarcodePiglet
BarcodePiglet

Reputation: 119

Get Total Average of Column in One Row SQL

I am an intern at an insurance company and was given an assignment to calculate the average value of premiums for x amount of customers.

I am using SQL Server 2014!!

Here below is my query:

SELECT  

    Customer.custno 
    , Customer.custid 
    , (Customer.firstname + ' ' + Customer.lastname) AS Client 
    , BasicPolInfo.enteredDate
    , BasicPolInfo.changedDate
    , BasicPolInfo.fulltermpremium AS Premium
    , AVG(BasicPolInfo.fulltermpremium) over(ORDER BY Customer.custno 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 'Total Premium'

FROM 
    
    Customer INNER JOIN
    BasicPolInfo ON Customer.CustId = BasicPolInfo.CustId INNER JOIN
    Transaction ON BasicPolInfo.PolId = Transaction.PolId INNER JOIN
    GeneralLedgerBranch ON Customer.GLBrnchCode = GeneralLedgerBranch.GLBrnchCode INNER JOIN
    GeneralLedgerDepartment ON Customer.GLDeptCode = GeneralLedgerDepartment.GLDeptCode INNER JOIN
    GeneralLedgerDivision ON Customer.GLDivCode = GeneralLedgerDivision.GLDivCode INNER JOIN
    Employee ON BasicPolInfo.ExecCode = Employee.EmpCode

WHERE   

    Customer.Active = 'Y'  
    AND BasicPolInfo.poltypelob = 'Homeowners'
    AND BasicPolInfo.status <> 'D'
    AND BasicPolInfo.fulltermpremium > '0.00'
    AND BasicPolInfo.polexpdate >= GetDate()


GROUP BY 
    
Customer.custno 
    , Customer.custid
    , Customer.firstname 
    , Customer.lastname 
    , BasicPolInfo.ChangedDate
    , BasicPolInfo.entereddate
    , Employee.firstname
    , Employee.lastname
    , Customer.enteredDate
    , basicpolinfo.fulltermpremium

ORDER BY 

    Customer.enteredDate ASC

So my table looks like this:

Custno | CustID | Client | EnteredDate | ChangedDate | Premium | TotalPremium

xxxxxx | xxxxxx | xxxxxx | xxxxxxxxxxx | xxxxxxxxxxx | 1350.38 | 1350.38

xxxxxx | xxxxxx | xxxxxx | xxxxxxxxxxx | xxxxxxxxxxx | 3517.00 | 2433.69

xxxxxx | xxxxxx | xxxxxx | xxxxxxxxxxx | xxxxxxxxxxx | 2094.00 | 2320.46

xxxxxx | xxxxxx | xxxxxx | xxxxxxxxxxx | xxxxxxxxxxx | 1811.00 | 2193.09

etc.

So what my boss wants is only one column of the average. For example, if this only had 4 customers, then it would ONLY show 2193.09 (inclusive of all the previous calculations to get there) and not show thousands of rows.

Total Premium

2193.09

Please comment if you have any questions. I tried my best honestly to explain it the best way I can...

We are trying to graphically represent only the 1 total premium average.

Thank you for your help!

Upvotes: 0

Views: 530

Answers (2)

DLeh
DLeh

Reputation: 24395

If I understand the problem correctly, you just need to wrap your entire query in:

SELECT AVG(Premium)
FROM 
( 

  <your query>

) as MyQuery

Upvotes: 2

xQbert
xQbert

Reputation: 35323

Different way of doing the same thing...

With CTE AS (SELECT  
    Customer.custno 
    , Customer.custid 
    , (Customer.firstname + ' ' + Customer.lastname) AS Client 
    , BasicPolInfo.enteredDate
    , BasicPolInfo.changedDate
    , BasicPolInfo.fulltermpremium AS Premium
    , AVG(BasicPolInfo.fulltermpremium) over(ORDER BY Customer.custno 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 'Total Premium'

FROM 

    Customer INNER JOIN
    BasicPolInfo ON Customer.CustId = BasicPolInfo.CustId INNER JOIN
    Transaction ON BasicPolInfo.PolId = Transaction.PolId INNER JOIN
    GeneralLedgerBranch ON Customer.GLBrnchCode = GeneralLedgerBranch.GLBrnchCode INNER JOIN
    GeneralLedgerDepartment ON Customer.GLDeptCode = GeneralLedgerDepartment.GLDeptCode INNER JOIN
    GeneralLedgerDivision ON Customer.GLDivCode = GeneralLedgerDivision.GLDivCode INNER JOIN
    Employee ON BasicPolInfo.ExecCode = Employee.EmpCode

WHERE   

    Customer.Active = 'Y'  
    AND BasicPolInfo.poltypelob = 'Homeowners'
    AND BasicPolInfo.status <> 'D'
    AND BasicPolInfo.fulltermpremium > '0.00'
    AND BasicPolInfo.polexpdate >= GetDate()


GROUP BY 

Customer.custno 
    , Customer.custid
    , Customer.firstname 
    , Customer.lastname 
    , BasicPolInfo.ChangedDate
    , BasicPolInfo.entereddate
    , Employee.firstname
    , Employee.lastname
    , Customer.enteredDate
    , basicpolinfo.fulltermpremium

ORDER BY 

    Customer.enteredDate ASC)

SELECT AVG(fulltermpremium) from cte

Upvotes: 0

Related Questions