March 23, 2009 at 1:50 pm
So I am hoping that there is a way to select data from the MEAN of a column, similar to using MIN() and MAX()...
Anyone have any idea?
Basically I have a table that subtracts the avg value (for all accounts) from the account's value - creating a 'difference' column. From there, I want to select the row with the MEAN (or basically the account with a difference closest to zero).
March 23, 2009 at 4:40 pm
Have you any sample data that you'd like to share along with the expected answer to make you thread perfectly clear? And, before you post any data, please take the time to study the article at the link in my signature below. Thanks.
--Jeff Moden
March 24, 2009 at 12:47 am
The Arithmetic Mean function is AVG().
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 24, 2009 at 4:09 am
Basically I have a table that subtracts the avg value (for all accounts) from the account's value - creating a 'difference' column. From there, I want to select the row with the MEAN (or basically the account with a difference closest to zero).
Assuming the account value is stored in a column named AccountValue, and the difference between each AccountValue and the arithmetic mean of all account values is stored in a column named Differerence, then I think the following will return the account whose value is closest to the arithmetic mean.
SELECT TOP 1 AccountValue, [Difference]
FROM AccountsTable
ORDER BY ABS([Difference])
However, I don't think you necessarily need the Difference column to do this calculation, unless you require it for other purposes. The following query will also return the row whose account value is closest to the arithmetic mean.
SELECT TOP 1 A.AccountValue, A.AccountValue - B.Mean
FROM AccountsTable A,
(SELECT AVG(AccountValue) AS Mean FROM AccountsTable) B
ORDER BY ABS(A.AccountValue - B.Mean)
March 24, 2009 at 7:07 am
andrewd.smith (3/24/2009)
Basically I have a table that subtracts the avg value (for all accounts) from the account's value - creating a 'difference' column. From there, I want to select the row with the MEAN (or basically the account with a difference closest to zero).
Assuming the account value is stored in a column named AccountValue, and the difference between each AccountValue and the arithmetic mean of all account values is stored in a column named Differerence, then I think the following will return the account whose value is closest to the arithmetic mean.
SELECT TOP 1 AccountValue, [Difference]FROM AccountsTable
ORDER BY ABS([Difference])
However, I don't think you necessarily need the Difference column to do this calculation, unless you require it for other purposes. The following query will also return the row whose account value is closest to the arithmetic mean.
SELECT TOP 1 A.AccountValue, A.AccountValue - B.MeanFROM AccountsTable A,
(SELECT AVG(AccountValue) AS Mean FROM AccountsTable) B
ORDER BY ABS(A.AccountValue - B.Mean)
Thanks, I think I will use the first query. Additional question, each account has a type (acct_type). Is there a way to get the Top 1 for each acct_type closest to the MEAN? Instead of returning the Top 1 for the entire table?
March 24, 2009 at 8:02 am
What is the table structure?
On which column(s) is the table's primary key based?
March 24, 2009 at 8:06 am
andrewd.smith (3/24/2009)
What is the table structure?On which column(s) is the table's primary key based?
The primary key is based off of [acct_num] (the account number). [acct_type] is four character text - and there are 5 possibilities for that field.
So ultimately I would like to see the Top 1 for each of the four account types.
March 24, 2009 at 9:13 am
OK - here's the table and test data I've used
CREATE TABLE #AccountsTable (
acct_num char(10) PRIMARY KEY,
acct_type char(4) NOT NULL,
acct_value decimal(10,2) NOT NULL,
difference decimal(10,2)
)
INSERT #AccountsTable(acct_num, acct_type, acct_value)
SELECT '12345678', 'A000', 12.34 UNION ALL
SELECT '12345677', 'A000', 15.05 UNION ALL
SELECT '12345676', 'A000', 11.90 UNION ALL
SELECT '12345675', 'A000', 4.10 UNION ALL
SELECT '92345670', 'B000', 112.00 UNION ALL
SELECT '92345671', 'B000', 80.50 UNION ALL
SELECT '92345672', 'B000', 75.30 UNION ALL
SELECT '92345673', 'B000', 0.98 UNION ALL
SELECT '02345672', 'C000', -5.30 UNION ALL
SELECT '02345673', 'C000', -15.70
This query updates the difference column (if you need it).
UPDATE #AccountsTable
SET difference = A.acct_value - B.acct_mean
FROM #AccountsTable A
JOIN (
SELECT acct_type, AVG(acct_value) AS acct_mean
FROM #AccountsTable
GROUP BY acct_type
) B ON (A.acct_type = B.acct_type)
This query returns the account whose value is closest to the arithmetic mean for each account type. This version of the query uses the difference column.
SELECT A.acct_num, A.acct_type, A.acct_value
FROM #AccountsTable A
WHERE A.acct_num IN (
SELECT TOP 1 B.acct_num
FROM #AccountsTable B
WHERE (B.acct_type = A.acct_type)
ORDER BY ABS(B.difference)
)
The following variation of the query doesn't use the difference column.
SELECT A.acct_num, A.acct_type, A.acct_value
FROM #AccountsTable A
WHERE A.acct_num IN (
SELECT TOP 1 B.acct_num
FROM #AccountsTable B
JOIN (
SELECT acct_type, AVG(acct_value) AS acct_mean
FROM #AccountsTable
GROUP BY acct_type
) C ON (B.acct_type = C.acct_type)
WHERE (B.acct_type = A.acct_type)
ORDER BY ABS(B.acct_value - C.acct_mean)
)
March 27, 2009 at 8:01 am
Thanks! Worked like a charm.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply