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

Posted on 2011-10-17
Last Modified: 2012-05-12
I have an Orders table that I need a query against. there are three rows I'm interested in


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.

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

Any suggestions?
Question by:AkAlan
    LVL 28

    Expert Comment

    Well, you will have to add OrderId to the Group by

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

    Open in new window

    LVL 6

    Author Comment

    Still get an error saying TotalOrderAmount is not part of an aggregate or Group By.
    LVL 2

    Accepted Solution

    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.
    LVL 18

    Expert Comment

    select SubQuery.buyerid, sum(SubQuery.TotalOrderAmount) FROM (SELECT orderid, buyerid, totalorderamount from orders group by  orderid, buyerid,totalorderamount  ) as SubQuery
    LVL 2

    Expert Comment

    @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 :)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now