April 22, 2014 at 5:53 am
Hi There,
I need to aggregate data and display it by ranking. I have a table called learning which has the following.
Coursegroup -- this higher level name (i.e. IT related skills development)
Title_Offering -- actual course attended (there could be multiple courses under the IT group)
People_Attended -- number of people that attended a particular title under a particular group.
simply put we can have 15 people attending training in the IT group however attending different titles. i.e. Sharepoint, SQL Development etc.
what I need to display is
Coursegroup Title_Offering People_Attended Ranking
IT Skills SharePoint 10 6 1
SQL Development 5 2
Web Development 4 3
---------------------------------------------------------------------------------------
Total 15
Management Skills Coaching for Growth 4 1
People Management 2 2
---------------------------------------------------------------------------------------
Total 6
---------------------------------------------------------------------------------------
Grand Total 21
Sample data:
Create table Learning
(CourseGroup Varchar(Max),
Title_Offering Varchar(Max),
No_people_Attended Int)
Insert Into Learning (CourseGroup, Title_Offering, No_people_Attended)
Values ('Information and Technology Related Skills Development', 'SAP01 -SAP Overview Level1', 32);
Insert Into Learning (CourseGroup, Title_Offering, No_people_Attended)
Values ('Information and Technology Related Skills Development', 'SQL Development', 10);
Insert Into Learning (CourseGroup, Title_Offering, No_people_Attended)
Values ('Information and Technology Related Skills Development', 'Sharepoint Development', 5);
Insert Into Learning (CourseGroup, Title_Offering, No_people_Attended)
Values ('Management and Leadership Skills Development', 'Coaching for Growth', 4);
Insert Into Learning (CourseGroup, Title_Offering, No_people_Attended)
Values ('Management and Leadership Skills Development', 'Handling difficult conversations', 2);
Insert Into Learning (CourseGroup, Title_Offering, No_people_Attended)
Values ('Risk Management', 'Anti-Money Laundering', 96);
Insert Into Learning (CourseGroup, Title_Offering, No_people_Attended)
Values ('Risk Management', 'Advanced Operational Risk', 15);
Insert Into Learning (CourseGroup, Title_Offering, No_people_Attended)
Values ('Risk Management', 'Role of a Risk Manager', 5);
April 22, 2014 at 6:17 am
This should help:
SELECTCourseGroup,
Title_Offering,
No_People_attended,
ROW_NUMBER() OVER (PARTITION BY CourseGroup ORder By No_People_Attended DESC) AS RowNumberResults,
RANK() OVER (PARTITION BY CourseGroup ORder By No_People_Attended DESC) AS RankedResults,
DENSE_RANK() OVER (PARTITION BY CourseGroup ORder By No_People_Attended DESC) AS DenseRankedResults
FROM Learning
MCITP SQL 2005, MCSA SQL 2012
April 22, 2014 at 6:18 am
This produced the desired output for me:
select CourseGroup, Title_Offering, No_people_Attended,
RANK() over(order by no_people_attended desc) as Ranking
from Learning l1
where No_people_Attended = (
select max(No_people_Attended) maxpeople
from Learning l2
where l1.CourseGroup = l2.CourseGroup
)
order by Ranking
April 22, 2014 at 6:25 am
Thanks for the feedback guys.
April 22, 2014 at 8:25 am
Here's a crazy query that does the whole thing, I think:
;with cte as (
select CourseGroup, Title_Offering, sum(No_people_Attended) No_people_Attended
, RANK() over(partition by CourseGroup order by sum(No_people_Attended) desc) as Ranking
from Learning
group by CourseGroup, Title_Offering
)
select CourseGroup, Title_Offering, No_people_Attended, Ranking
from (
select CourseGroup, Title_Offering, No_people_Attended --, Ranking
, (rank() over(partition by Ranking order by No_people_Attended desc)) Ranking
, cast(ranking as varchar(10)) as rank_sort from cte
union all
select 'Rank ' + cast(Ranking as varchar(10)) + ' Total'
, ''
, sum(No_people_Attended) as No_people_Attended
, null
, cast(Ranking as varchar(10)) + 'T'
from cte
group by ranking
) q
order by rank_sort, ranking asc
April 23, 2014 at 12:08 am
Thank you that is exactly what I was looking for
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply