?
Solved

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

Posted on 2011-10-17
5
Medium Priority
?
863 Views
Last Modified: 2012-05-12
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?
0
Comment
Question by:AkAlan
5 Comments
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 36982900
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
 
LVL 6

Author Comment

by:AkAlan
ID: 36982943
Still get an error saying TotalOrderAmount is not part of an aggregate or Group By.
0
 
LVL 2

Accepted Solution

by:
mensoid earned 2000 total points
ID: 36982966
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
 
LVL 18

Expert Comment

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

Expert Comment

by:mensoid
ID: 36986449
@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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

862 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