September 10, 2010 at 8:23 am
As per my other thread, I'm new to SQL and I think I'm learning enough but having some problems trying to do four separate calculations.
I really appreciated the help from Wayne and Craig. And I need some help again.
CREATE TABLE [dbo].[2010 Master Survey Results](
[ID] [int] IDENTITY(1,1) NOT NULL,
[BENCHMARK] [float] NULL,
[YOUR_STATES_JOB_TITLE] [nvarchar](255) NULL,
[Number_of_INC] [float] NULL,
[AVG_SALARY] [money] NULL,
[PAY_RANGE_MIN] [money] NULL,
[PAY_RANGE_MIDPT] [money] NULL,
[PAY_RANGE_MAX] [money] NULL,
[PERCENTILE_25TH] [money] NULL,
[MEDIAN] [money] NULL,
[PERCENTILE_75TH] [money] NULL,
[MATCH_LEVEL] [nvarchar](255) NULL,
[CLASS_LEVEL] [nvarchar](255) NULL,
[STATE] [nvarchar](255) NULL
This is my main table, that is where all my data is stored.
What I'm trying to do is is simple. Multiply [Number_of_INC] by [AVG_SALARY] for all 26 rows of each BENCHMARK (Think about having 26 different counties or states for each BENCHMARK number) and then SUM that calculation. At that point, I want to divide that number by the SUM of [Number_of_INC], already calculated in my existing code below.
SELECT BENCHMARK,
SUM(Number_of_INC) AS [Sum of Incumbents],
AVG(Number_of_INC) AS [Average of Incumbents],
AVG(AVG_SALARY) AS [Average Salary],
AVG(PAY_RANGE_MIN) AS [Average Pay Range Min],
AVG(PAY_RANGE_MIDPT) AS [Average Pay Range Mid],
AVG(PAY_RANGE_MAX) AS [Average Pay Range Max],
AVG(PERCENTILE_25TH) AS [Average Percentile 25th],
AVG(MEDIAN) AS [Average Median],
AVG(PERCENTILE_75TH) AS [Average Percentile 75th]
FROM [2010 Master Survey Results]
GROUP BY BENCHMARK
ORDER BY BENCHMARK
To illustrate my calculation, I've included a brief, small image of my calculations.
If I can just figure out how to do this in the query, without having to create another table, I can get the other three calculations needed as I would just be changing the [AVG_SALARY] to the Pay Ranges.
Thanks for your time!
Matt
September 10, 2010 at 8:46 am
You mean like this?
SELECT BENCHMARK,
SUM(Number_of_INC) AS [Sum of Incumbents],
AVG(Number_of_INC) AS [Average of Incumbents],
AVG(AVG_SALARY) AS [Average Salary],
AVG(PAY_RANGE_MIN) AS [Average Pay Range Min],
AVG(PAY_RANGE_MIDPT) AS [Average Pay Range Mid],
AVG(PAY_RANGE_MAX) AS [Average Pay Range Max],
AVG(PERCENTILE_25TH) AS [Average Percentile 25th],
AVG(MEDIAN) AS [Average Median],
AVG(PERCENTILE_75TH) AS [Average Percentile 75th],
SUM([Number_of_INC] * [AVG_SALARY]) / SUM(Number_of_INC) AS NewColumn -- may require divide-by-zero check
FROM [2010 Master Survey Results]
GROUP BY BENCHMARK
Which I reckon reads better like this:
SELECT d.BENCHMARK,
d.[Sum of Incumbents],
d.[Average of Incumbents],
d.[Average Salary],
d.[Average Pay Range Min],
d.[Average Pay Range Mid],
d.[Average Pay Range Max],
d.[Average Percentile 25th],
d.[Average Median],
d.[Average Percentile 75th],
d.NewColumn / d.[Sum of Incumbents]
FROM (
SELECT BENCHMARK,
SUM(Number_of_INC) AS [Sum of Incumbents],
AVG(Number_of_INC) AS [Average of Incumbents],
AVG(AVG_SALARY) AS [Average Salary],
AVG(PAY_RANGE_MIN) AS [Average Pay Range Min],
AVG(PAY_RANGE_MIDPT) AS [Average Pay Range Mid],
AVG(PAY_RANGE_MAX) AS [Average Pay Range Max],
AVG(PERCENTILE_25TH) AS [Average Percentile 25th],
AVG(MEDIAN) AS [Average Median],
AVG(PERCENTILE_75TH) AS [Average Percentile 75th],
SUM([Number_of_INC] * [AVG_SALARY]) AS NewColumn
FROM [2010 Master Survey Results]
GROUP BY BENCHMARK
) d
ORDER BY d.BENCHMARK
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 10, 2010 at 9:22 am
Chris Morris-439714 (9/10/2010)
You mean like this?Which I reckon reads better like this:
I know. I'm sorry. I apologize This is the second time I have ever done SQL coding so I'm really just piecing it together!
I thank you for your help. Here's your code and my edits...
SELECT d.BENCHMARK,
d.[Sum of Incumbents],
d.[Average of Incumbents],
d.[Average Salary],
d.[Average Pay Range Min],
d.[Average Pay Range Mid],
d.[Average Pay Range Max],
d.[Average Percentile 25th],
d.[Average Median],
d.[Average Percentile 75th],
d.[Average Salary, Incumbent Weighted] / d.[Sum of Incumbents],
d.[Average Pay Range Minimum, Incumbent Weighted] / d.[Sum of Incumbents],
d.[Average Pay Range Midpoint, Incumbent Weighted] / d.[Sum of Incumbents],
d.[Average Pay Range Maximum, Incumbent Weighted] / d.[Sum of Incumbents]
FROM (
SELECT BENCHMARK,
SUM(Number_of_INC) AS [Sum of Incumbents],
AVG(Number_of_INC) AS [Average of Incumbents],
AVG(AVG_SALARY) AS [Average Salary],
AVG(PAY_RANGE_MIN) AS [Average Pay Range Min],
AVG(PAY_RANGE_MIDPT) AS [Average Pay Range Mid],
AVG(PAY_RANGE_MAX) AS [Average Pay Range Max],
AVG(PERCENTILE_25TH) AS [Average Percentile 25th],
AVG(MEDIAN) AS [Average Median],
AVG(PERCENTILE_75TH) AS [Average Percentile 75th],
SUM([Number_of_INC] * [AVG_SALARY]) AS [Average Salary, Incumbent Weighted],
SUM([Number_of_INC] * [PAY_RANGE_MIN]) AS [Average Pay Range Minimum, Incumbent Weighted],
SUM([Number_of_INC] * [PAY_RANGE_MIDPT]) AS [Average Pay Range Midpoint, Incumbent Weighted],
SUM([Number_of_INC] * [PAY_RANGE_MAX]) AS [Average Pay Range Maximum, Incumbent Weighted]
FROM [2010 Master Survey Results]
GROUP BY BENCHMARK
) d
ORDER BY d.BENCHMARK
Now I just need to figure out how to name those additional columns. Back to googling!
Thank you Chris!
Matt
September 10, 2010 at 12:31 pm
So, I am a bit confused.
My data is putting out a lot of decimal places when I really would like to have this in money format. My table is showing the columns in the money format but my results are displaying with long decimals.
How can I change this?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply