T-Sql Get sum over sub table grouped on two rows

I have an Orders table that I need a query against. there are three rows I'm interested in


OrderID
BuyerID
TotalOrderAmount

The TotalOrderAmount has the same amount  repeated over every OrderNumber.

So here is an exapmle of the data

OrderID     BuyerID       TotalOrderAmount
1                       1                     2.50
1                       1                     2.50
1                       1                     2.50
2                       1                     3.00
2                       1                     3.00
3                       2                     1.00
3                       2                     1.00

Ultimately I need to show the Sum(TotalOrderAmount) Grouped By OrderID but also ultimately Grouped By the BuyerID, I don't want the output to show the OrderID grouping. Here is what I would need to see based on my data:

BuyerID     sumOfOrders
  1                      5.50
  2                      1.00

I am trying to use something like the following but keep getting an error saying the Column OrderID is invalid because it is not used as an aggregate or in the Group By.

SELECT
BuyerID,
 SUM(TotalOrderAmount) Over(Partition by OrderID) as sumOfOrders
Group By BuyerID

Any suggestions?
LVL 6
AkAlanAsked:
Who is Participating?
 
mensoidCommented:
Select BuyerId,Sum(TotalOrderAmount) from (SELECT Distinct OrderId, BuyerId, TotalOrderAmount
FROM Orders) group by BuyerId

This should do it, it's grabs the distinct values first and then sums them to remove the duplicates.
0
 
sammySeltzerCommented:
Well, you will have to add OrderId to the Group by

SELECT
BuyerID,
 SUM(TotalOrderAmount) Over(Partition by OrderID) as sumOfOrders
Group By BuyerID, OrderID

Open in new window

0
 
AkAlanAuthor Commented:
Still get an error saying TotalOrderAmount is not part of an aggregate or Group By.
0
 
deightonprogCommented:
select SubQuery.buyerid, sum(SubQuery.TotalOrderAmount) FROM (SELECT orderid, buyerid, totalorderamount from orders group by  orderid, buyerid,totalorderamount  ) as SubQuery
0
 
mensoidCommented:
@deighton, without a group by subquery.buyerid your query will fail on the sum statement, and once you add it it'll be the same as my post :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.