Nhibernate QueryOver - .SelectCount() with predicate

Question

I need to select count of row with a condition:

Query to collect the full count:

var searchs = searchQuery.SelectList
                    (list => list

                                .SelectGroup(order => order.Id).WithAlias(() => groupResult.GlobalId)
                                .SelectCount(() => _transaction.ReturnStatus).WithAlias(() => groupResult.DeclineCount)
                    )

I need count of transactions that equals 201. Something like this:

.SelectCount(() => _transaction.ReturnStatus == 201).WithAlias(() => groupResult.DeclineCount) //runtime error

Thanks in advance!

PS: Original SQL Query:

SELECT TOP 100
        globalOrd.ID AS GlobalId ,
        SUM(CASE WHEN transact.returnStatus = 201 THEN 1
                 ELSE 0
            END) AS DeclineCount
FROM    Orders.Global globalOrd
        INNER JOIN Orders.TransactionDetail transactDet ON globalOrd.ID = transactDet.DetailID
        INNER JOIN Orders.[Transaction] transact ON transactDet.TransactionID = transact.ID
GROUP BY globalOrd.ID

Answer 1

If you don't need the total count in the same query you can simply add in the restriction before the SelectList:

var searchs = searchQuery.SelectList
    (list => list
                .Where(() => _transaction.ReturnStatus == 201)
                .SelectGroup(order => order.Id).WithAlias(() => groupResult.GlobalId)
                .SelectCount(() => _transaction.ReturnStatus).WithAlias(() => groupResult.DeclineCount)
    )

If however, you want both the total and the restricted count, you would have to use a SqlProjection for the latter doing something like:

 SUM(CASE {alias}.ReturnStatus WHEN 201 THEN 1 ELSE 0 END)