Calculating Median value with NHibernate

NHibernate provides a lot of useful projections to make your life easier in case of statistics queries. But it does not provide you with median value, for example and this is what was needed for me recently. (Just in case here is a link for the definition of median)

To simplify the example, lets imagine that I`m querying over one table 'Sales' that have a reference to 'Employee' object (the person who made that sale) and a 'Volume' column.

The requirement is to produce a report with a total number of sales, average, total and median volumes for each employee.

As always, here is a code:

var sql = @"(SELECT AVG([Volume])
FROM (
  SELECT
    [Volume],
    ROW_NUMBER() OVER (ORDER BY [Volume] ASC, Id ASC) AS RowAsc,
    ROW_NUMBER() OVER (ORDER BY [Volume] DESC, Id DESC) AS RowDesc
  FROM Sales
  WHERE [Volume] > 0 and [EmployeeId] = ?1
  ) ra
WHERE RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)
)";

var medianFunc = new SQLFunctionTemplate(NHibernateUtil.Double, sql);

var querry = Session.QueryOver(); //and join needed aliases

var resultList = querry
                  .SelectList(builder => builder
                                .Select(Projections.Group(s => s.Employee)).WithAlias(() => result.Employee)
                                .Select(Projections.Count(s => s.Id)).WithAlias(() => result.TotalSales)
                                .Select(Projections.Sum(s => s.Volume)).WithAlias(() => result.TotalVolume)
                                .Select(Projections.Avg(s => s.Volume)).WithAlias(() => result.AverageVolume)
                                .Select(Projections.SqlFunction(medianFunc, NHibernateUtil.Double, Projections.Property("Employee"))
                                     .WithAlias(() => result.MedianVolume))
                              )
                  .TransformUsing(Transformers.AliasToBean())
                  .List();

protected class Result
{
  public int TotalSales{ get; set; }

  public Money TotalVolume { get; set; }

  public double AverageVolume { get; set; }

  public double MedianVolume { get; set; }

  public Employee Employee { get; set; }
}