ams00601 (5/27/2016)
Hi all,looking at the example data below, is it possible to sum up just those rows where ProductID = 1 and leave these plits for the other rows as they are and have it all displaying in one output?
select customerID, productID, orderDate, orderAmount from Orders
customerID productID orderDate orderAmount
----------- ----------- ----------------------- ---------------------
1 1 2007-01-01 00:00:00.000 20.00
1 2 2007-01-02 00:00:00.000 30.00
1 2 2007-01-05 00:00:00.000 23.00
1 3 2007-01-04 00:00:00.000 18.00
2 1 2007-01-03 00:00:00.000 74.00
2 1 2007-01-06 00:00:00.000 34.00
2 2 2007-01-08 00:00:00.000 10.00
many thanks for reading
CREATE TABLE #orders
(
customerIDINT
,productIDINT
,orderDate DATETIME
,orderAmount DECIMAL(4,2)
)
INSERT INTO #orders VALUES
(1, 1,'2007-01-01 00:00:00.000', 20.00)
,(1, 2 ,'2007-01-02 00:00:00.000', 30.00)
,(1, 2,'2007-01-05 00:00:00.000', 23.00)
,(1, 3,'2007-01-04 00:00:00.000', 18.00)
,(2, 1,'2007-01-03 00:00:00.000', 74.00)
,(2, 1,'2007-01-06 00:00:00.000', 34.00)
,(2, 2, '2007-01-08 00:00:00.000', 10.00)
SELECT
customerID
,s.productID
,orderDate
,orderamount = ISNULL(sp.AggSales,s.orderAmount)
FROM #orders s
LEFT JOIN
(
SELECT
AggSales = SUM(orderamount)
,productID
FROM #orders
WHERE productID = 1
GROUP BY
productID
) sp ON sp.productID = s.productID
DROP TABLE #orders
This is one way of doing it.
@Roshan, your method won't work as it is because you need to GROUP BY the non-aggregated columns in the first SELECT.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537