October 7, 2010 at 3:31 pm
I don't know what to call this exactly, and I'd rather not try and explain my rather complicated problem, so consider this example. I have a table of employees with data like Name, Gender, Age, etc that is linked to a table of employee groups with data like GroupA, GroupB, etc. Each employee can be associated to only 1 group, i.e. EmployeeGroups would be the parent table in a many-to-one relationship.
I want to make a report that generates a individual page for each employee listing some information about each employee, including their group, and then giving the Average, or Max age in each individual group.
Here is so data to get my point out and what I would expect as a result
-- Data
Jim, M, 25, GroupA
Sam, M, 15, GroupA
Sue, F, 26, GroupB
-- Result (paged format)
Name: Jim
Sex: Male
Age: 25
Group: A
Average Age In Group: 20
Maximum Age In Group: 25
Name: Sam
Sex: Male
Age: 15
Group: A
Average Age In Group: 20
Maximum Age In Group: 25
Name: Sue
Sex: Female
Age: 26
Group: A
Average Age In Group: 26
Maximum Age In Group: 26
So that is what I am looking for. I have to do a lot more stuff like charts for each person on other columns, but I believe the method is the same. I have decided to display all records in a LIST object and group on employeeID. I can then have all data repeated for each person. But I need to get an average that isn't grouped by the employeeID so it somehow has to be placed overtop of the list object in the design view and have a scope of EmployeeGroup. I can't seem to figure out how to do this.
Its pretty much my average function is being done on a employee by employee basis, where as I need it to not take into account the list's grouping.
October 7, 2010 at 4:45 pm
DECLARE @test TABLE (
RowID INT IDENTITY PRIMARY KEY CLUSTERED,
Name varchar(10),
Gender char(1),
Age tinyint,
[Group] char(1));
INSERT INTO @test
SELECT 'Jim', 'M', 25, 'A' UNION ALL
SELECT 'Sam', 'M', 15, 'A' UNION ALL
SELECT 'Sue', 'F', 26, 'B' ;
SELECT Name,
Gender,
Age,
[Group],
AvgAge = avg(Age) OVER (PARTITION BY GENDER),
MaxAge = max(Age) OVER (PARTITION BY GENDER)
FROM @test
ORDER BY RowID;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply