una - sumar por filas sql server

Suma condicional en grupo por consulta MSSQL

Question

Tengo una tabla OrderDetails con el siguiente esquema:

----------------------------------------------------------------
| OrderId | CopyCost | FullPrice | Price | PriceType |
----------------------------------------------------------------
| 16 | 50 | 100 | 50 | CopyCost |
----------------------------------------------------------------
| 16 | 50 | 100 | 100 | FullPrice |
----------------------------------------------------------------
| 16 | 50 | 100 | 50 | CopyCost |
----------------------------------------------------------------
| 16 | 50 | 100 | 50 | CopyCost |
----------------------------------------------------------------

Necesito una consulta que suponga la tabla anterior en una nueva tabla con el siguiente esquema:

----------------------------------------------------------------
| OrderId | ItemCount | TotalCopyCost | TotalFullPrice |
----------------------------------------------------------------
| 16 | 4 | 150 | 100 |
----------------------------------------------------------------

Actualmente estoy usando un grupo por en el pedido. Id al recuento del artículo. Pero no sé cómo suponer condicionalmente los valores CopyCost y FullPrice.

Cualquier ayuda sería muy apreciada.

Saludos Freddie

Answer 1

Podrías usar:

SELECT
OrderId,
Count(1) as ItemCount,
SUM(CASE WHEN PriceType = ''CopyCost''
THEN CopyCost ELSE 0 END) AS TotalCopyCost,
SUM(CASE WHEN PriceType = ''FullPrice''
THEN FullPrice ELSE 0 END) AS TotalFullPrice
FROM OrderDetails
GROUP BY OrderId

Answer 2

Prueba esta consulta

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

FIDDLE SQL :

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

Answer 3

También podrías intentar ...

select A.OrderID, A.ItemCount,B.TotalCopyCost, C.TotalFullPrice
from (select OrderID, count(*) as ItemCount from orderdetails) as A,
(select OrderID, sum(CopyCost) as TotalCopyCost from orderdetails where PriceType = ''CopyCost'') as B,
(select OrderID, sum(FullPrice) as TotalFullPrice from orderdetails where PriceType = ''FullPrice'') as C
where A.OrderID = B.OrderID

SQLFiddle: http://sqlfiddle.com/#!2/946af/6

Answer 4

Tratar

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