How can i combine records with same customerID into one row

Transact-SQL
1

Hi, I am trying to combine records with the same customerID into one row. I'm using the following query:

image

But I'm getting results in which there are multiple rows for the same customerID. I'd like to combine the records with the same customerID into one row, and have the revenue aggregated.

Any help will be appreciated. Thanks!

2

If you select the sum of the revenue only it should work:

SELECT ur.CustomerID, SUM(vpt.RevNoTax) AS RevNoTax
....
GROUP BY ur.CustomerID

If you want the order_Date you could add that for example by MAX(OHR.ORDER_DATE) AS LastOrderDate.

Be sure you understand why you use with (nolock), I would not use it unless I can defend it. Watch Pinal Dave explain it in 60 seconds:

2 Likes
3

Thanks RogierPronk!