Is it always possible to do something in SQL if you could do it in Linq?

Question

I'm trying to write a SQL Statement that should function like the below Linq query. Mainly, I want to sum two columns (Cash and Check) out of one column (Value * Quantity) based on a certain condition (Type == "Check"). So, if the Type is "Check", then put the value in the Check column, otherwise put it in the Cash column.

Linq:

from ac in m_DataContext.acquisitions
join ai in m_DataContext.acquireditems on ac.Identifier equals ai.AcquisitionIdentifier
group ai by ac.SessionIdentifier into g
select new
{
    SessionIdentifier = g.Key,
    Cash = g.Where(ai => ai.Type != "Check").Sum(ai => (double?)ai.Value * (int?)ai.Quantity) ?? 0,
    Check = g.Where(ai => ai.Type == "Check").Sum(ai => (double?)ai.Value * (int?)ai.Quantity) ?? 0,
    CheckQuantity = g.Where(ai => ai.Type == "Check").Sum(ai => (int?)ai.Quantity) ?? 0
};

SQL:

SELECT a.SessionIdentifier, a.Checks as CheckQuantity, ???? as Cash, ???? as Check
FROM Acquisitions ac
INNER JOIN AcquiredItems ai ON ai.AcquisitionIdentifier = ac.Identifier
GROUP BY a.SessionIdentifier

Is this possible?

Answer 1

You can do it like this:

select sum(case when type = 'cash' then Value * Quantity else 0 end) as Cash,
    sum(case when type = 'check' then Value * Quantity else 0 end) as Check
from ...