CODEWITHSUNDEEP
sql-servert-sql
Pepe
Pepe

Reputation: 111

Get closest value to AVG TSQL function

I have a select like this:

SELECT
    FORMAT(AVG([DC].[ContractedAmount]) , '$###,###,###,###.##') AS [AverageContractedAmount]
FROM 
    [DesignCustomer] AS [DC]
INNER JOIN 
    [Design] AS [D] ON [DC].[DesignKey] = [D].[DesignKey]
INNER JOIN 
    [Task] AS [T] ON [D].[DesignKey] = [t].[DesignKey]
INNER JOIN 
    [ProjectDesign] AS [PD] ON [D].[DesignKey] = [PD].[DesignKey]
INNER JOIN 
    [Project] AS [P] ON [PD].[ProjectKey] = [P].[ProjectKey]
INNER JOIN 
    [Address] AS [A] ON [A].[AddressGuid] = [P].[ProjectGuid]

As you can see I get the Average of Contracted Amount. I get something like this:

+---------------------------+
| [AverageContractedAmount] |
+---------------------------+
| $1,000.00                 |
+---------------------------+

Now I want to get project who is more closest to that value

For example if I have 5 projects in project table like:

+----------------+
|  ProjectName   |
+----------------+
| First Project  |
| Second Project |
| Third Project  |
| Four Project   |
| Five Project   |
+----------------+

Relation of [DC] with project is something like this:

+----------------+------------------+
|  ProjectName   | ContractedAmount |
+----------------+------------------+
| First Project  |              500 |
| Second Project |              700 |
| Third Project  |              300 |
| Four Project   |              950 |
| Five Project   |              800 |
+----------------+------------------+

I want the query to return the Four Project Name because its ContractedAmount value is the closest to the AVG value. How can I achieve this? Regards

Upvotes: 0

Views: 156

Answers (4)

Leia
Leia

Reputation: 190

It seems that you've already figured out getting your average, so taking your sample tables, but fudged to get an average of $1000, I wrote a single select statement using the average to get your answer. For this answer, I've just calculated the average from the table rather than your code in order to get the variable @AVERAGE.

    CREATE TABLE #Amts (Project VARCHAR(20), Amount INT);

    INSERT INTO #Amts 
    VALUES

    ('One Project', 500),
    ('Two Project', 1500),
    ('Three Project', 300),
    ('Four Project', 1700),
    ('Five Project', 1100),
    ('Six Project', 900)        ;

    DECLARE @AVERAGE INT = (SELECT AVG(Amount) FROM #Amts) -- $1000

    SELECT    TOP 1 Project -- Since you said whichever project is suitable, this should be fine. 
    FROM      #Amts AS A
    WHERE     ABS(A.Amount - @AVERAGE) = (SELECT MIN(ABS(Amin.Amount - @AVERAGE)) FROM #Amts AS Amin)
    ORDER BY  Project

    DROP TABLE #Amts

This gives you the answer of "Five Project".

Upvotes: 1

JNevill
JNevill

Reputation: 50200

Without putting much thought into this, you can just dump that into a subquery and subtract, sort by the difference, and keep the top result:

SELECT TOP 1
    project_name,

FROM Project
ORDER BY Abs(ContractedAmount - 
        (
            SELECT
            AVG([DC].[ContractedAmount]) AS [AverageContractedAmount]
             FROM [DesignCustomer] AS [DC]
                             INNER JOIN [Design] AS [D] ON [DC].[DesignKey] = [D].[DesignKey]
                             INNER JOIN [Task] AS [T] ON [D].[DesignKey] = [t].[DesignKey]
                             INNER JOIN [ProjectDesign] AS [PD] ON [D].[DesignKey] = [PD].[DesignKey]
                             INNER JOIN [Project] AS [P] ON [PD].[ProjectKey] = [P].[ProjectKey]
                             INNER JOIN [Address] AS [A] ON [A].[AddressGuid] = [P].[ProjectGuid]
        )) ASC

Upvotes: 1

MK_
MK_

Reputation: 1169

Strictly closest would be:

; WITH AvgAmt AS (
    SELECT AVG([DC].[ContractedAmount]) AS [AverageContractedAmount]
    FROM [DesignCustomer] AS [DC]
        INNER JOIN [Design] AS [D] ON [DC].[DesignKey] = [D].[DesignKey]
        INNER JOIN [Task] AS [T] ON [D].[DesignKey] = [t].[DesignKey]
        INNER JOIN [ProjectDesign] AS [PD] ON [D].[DesignKey] = [PD].[DesignKey]
        INNER JOIN [Project] AS [P] ON [PD].[ProjectKey] = [P].[ProjectKey]
        INNER JOIN [Address] AS [A] ON [A].[AddressGuid] = [P].[ProjectGuid]
)
SELECT TOP(1) P.ProjectName, DC.ContractedAmount
FROM [DesignCustomer] AS [DC]
    INNER JOIN [Design] AS [D] ON [DC].[DesignKey] = [D].[DesignKey]
    INNER JOIN [ProjectDesign] AS [PD] ON [D].[DesignKey] = [PD].[DesignKey]
    INNER JOIN [Project] AS [P] ON [PD].[ProjectKey] = [P].[ProjectKey]
ORDER BY ABS(AvgAmt.[AverageContractedAmount] - ContractedAmount);

Depending on your data, you might want to exclude a few INNER JOIN's from the CTE itself simply to improve performance if the data doesn't require it.

The solution can also be easily parametrized to select closest @n contracted amounts instead of the closest one (converting SELECT TOP(1) to SELECT TOP(@n)).

As an addition, if there are multiple projects with the same difference to the average contracted amount, you might want to add some other columns into ORDER BY to break the tie. This is something you can decide on as the one with the knowledge of what data represents and what you expect from it.

Upvotes: 0

user1443098
user1443098

Reputation: 7665

using DENSE_RANK:

WITH getavg AS (
SELECT AVG([DC].[ContractedAmount]) OVER() AS [AverageContractedAmount]
, p.projectname, p.contractedamount
 FROM [DesignCustomer] AS [DC]
                     INNER JOIN [Design] AS [D] ON [DC].[DesignKey] = [D].[DesignKey]
                     INNER JOIN [Task] AS [T] ON [D].[DesignKey] = [t].[DesignKey]
                     INNER JOIN [ProjectDesign] AS [PD] ON [D].[DesignKey] = [PD].[DesignKey]
                     INNER JOIN [Project] AS [P] ON [PD].[ProjectKey] = [P].[ProjectKey]
                     INNER JOIN [Address] AS [A] ON [A].[AddressGuid] = [P].[ProjectGuid]

),

ranked as (
SELECT projectname, contractedamount
DENSE_RANK() OVER(ORDER BY ABS([AverageContractedAmount]-contractedamount)) AS dr
FROM getavg)

SELECT * FROM ranked
WHERE dr <= 5

Upvotes: 0

Related Questions