CODEWITHSUNDEEP
sqlt-sqlsql-server-2012subquery
LacOniC
LacOniC

Reputation: 944

SubSelects for Order and Count

I have a select like this:

SELECT Faculty,Department,Season,Student,Score FROM Table

I want two new columns like this:

Sample Result Set

NumberInSeason is order of scores in that faculty, department and season.

TotalInSeason is count of students in that faculty, department and season.

I think i need subselects but can not figure out right now. Any help?

Upvotes: 0

Views: 40

Answers (2)

Andrew Karakotov
Andrew Karakotov

Reputation: 664

Window functions should do the task:

SELECT 
  Faculty, 
  Department, 
  Season, 
  Student, 
  Score, 
  DENSE_RANK() OVER (PARTITION BY Faculty, Department, Season ORDER BY Score DESC) AS NumberInSeason, 
  COUNT(*) OVER (PARTITION BY Faculty, Department, Season) AS TotalInSeason 
FROM Table

Upvotes: 2

Nir-Z
Nir-Z

Reputation: 869

Try this (not tested)

SELECT  a.Faculty,a.Department,a.Season,a.Student,Score,count(b.*)  as NumberInSeason , 
SELECT (count(*) FROM table c ON c.Faculty=a.Faculty AND c.Department=a.Department,c.Season=a.Season) AS TotalInSeason 
FROM Table a
LEFT JOIN Table b ON a.Faculty=b.Faculty and a.Department=b.Department and a.Season=b.Season and a.score<=b.score
group by a.Faculty,a.Department,a.Season,a.Student,a.Score

Upvotes: 0

Related Questions