Conditional sum in Group By query MSSQL

Solution 1:

Try

SELECT OrderId, 
       COUNT(*) ItemCount,
       SUM(CASE WHEN PriceType = 'CopyCost' THEN Price ELSE 0 END) TotalCopyCost,
       SUM(CASE WHEN PriceType = 'FullPrice' THEN Price ELSE 0 END) TotalFullPrice
  FROM OrderDetails
 GROUP BY OrderId

SQLFiddle

Solution 2:

Try this query

select 
   orderId, 
   count(*) as cnt, 
   sum(if(pricetype='CopyCost', CopyCost, 0)) as totalCopyCost,
   sum(if(pricetype='FullPrice', FullPrice, 0)) as totalFullPrice
from 
   tbl
group by 
   orderId

SQL FIDDLE:

| ORDERID | CNT | TOTALCOPYCOST | TOTALFULLPRICE |
--------------------------------------------------
|      16 |   4 |           150 |            100 |