T-SQL aggregating

Question

T-SQL aggregating

ams00601

SSC-Addicted

Points: 473
More actions
May 27, 2016 at 4:48 am

#319043

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

joeroshan SSChampion Points: 10377 More actions · Answer 1

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

Is this what you are looking for?

Select customerID, productID, max(orderDate) orderDate, Sum(orderAmount) orderAmount from Orders Where productID=1 group by customerID, productID

Union all

select customerID, productID, orderDate, orderAmount from Orders Where productID<>1

Edit: added group by

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Neil Burton SSC-Insane Points: 23000 More actions · Answer 2

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.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

joeroshan SSChampion Points: 10377 More actions · Answer 3

Nice, catch, edited and added group by if at all OP is interested still.

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]