51674

How to use aggregate SUM function on result of SUM in INNER JOIN?

This is my SQL query which joins two tables:

SELECT `o`.`orderID`, SUM(i.partialAmount) AS `amount` FROM `OrderTable` AS `o` INNER JOIN `Item` AS `i` ON i.orderID = o.orderID GROUP BY `o`.`orderID`

Items are connected to orders via 1-to-many relationship. Each item has a partialAmount column and I am adding up those partial amounts to get a total amount per each order.

I would like to get a total amount for all orders though, how to do that?

So I would like something like: SUM(amount) AS totalAmount but this doesn't work:

SELECT `o`.`orderID`, SUM(i.partialAmount) AS `amount`, SUM(amount) AS `totalAmount` FROM `OrderTable` AS `o` INNER JOIN `Item` AS `i` ON i.orderID = o.orderID GROUP BY `o`.`orderID`

I don't want to use WITH ROLLUP - because I don't want an extra row.

Expected output:

----------------------------------------------------------------------- | orderID | amount | totalAmount | ----------------------------------------------------------------------- | 1 | 2 | 5 | ----------------------------------------------------------------------- | 2 | 3 | 5 | -----------------------------------------------------------------------

Answer1:

If all you want is a total line, you can do something like this code. There are other approaches as well, depending on how if you need to filter the selected orders

SELECT `o`.`orderID`, SUM(i.partialAmount) AS `amount` FROM `OrderTable` AS `o` INNER JOIN `Item` AS `i` ON i.orderID = o.orderID GROUP BY `o`.`orderID` UNION SELECT 999999,sum(i.partialAmount) as 'Amount' FROM Item i

Answer2:

This will be a hit on performance (and assumes that every row from Item has an order):

SELECT `o`.`orderID`, SUM(i.partialAmount) AS `amount`, (SELECT SUM(partialAmount) FROM `Item`) TotalAmount FROM `OrderTable` AS `o` INNER JOIN `Item` AS `i` ON i.orderID = o.orderID GROUP BY `o`.`orderID`

Answer3:

I'd highly advise doing this kind of thing in the application rather than SQL as it is repitition of work, but it can be achieved as follows:

SELECT OrderTable.OrderID, SUM(PartialAmount) AS Amount, TotalAmount FROM OrderTable, Item, (SELECT SUM(PartialAmount) AS TotalAmount FROM Item) AS total WHERE Item.OrderID = Ordertable.OrderID GROUP BY OrderTable.OrderID, TotalAmount

I can't check the execution plan to compare this to the option of the Subselect posted in another answer by Lamak, but SQL Server definitely optimises the cross apply much better than the subselect.

Answer4:

If you want the total value on every line you could use sum() over function which would give you both totals http://msdn.microsoft.com/en-us/library/ms189461.aspx

for running total

sum(amount) over ()

for partial total

sum(amount) over (partition by o.orderid)

would look like this

SELECT distinct `o`.`orderID`, SUM(i.partialAmount) over (partition by orderid) AS `amount`, SUM(i.partialAmount) over () AS `totalAmount` FROM `OrderTable` AS `o` INNER JOIN `Item` AS `i` ON i.orderID = o.orderID

Recommend

  • set button onclick in listview android
  • How to select sums of certain 'id' and devide the result by groups
  • How to conditionally convert inches to cm in MySQL (or similar conversions during SELECT)?
  • PHP Multidimensional Array Count
  • Adding a pivot to a rollup
  • How to sort and add my database record? [duplicate]
  • Get count of row and sum group by quarter of date, if another column doesnt exits a value in SQL Ser
  • R mlogit on my data giving error 'system is computationally singular?
  • SQL: Getting the physical size of a subset of a table
  • pip in virtualenv gets ConnectTimeoutError
  • jQuery: add elements until a particular height is reached
  • Reduction and collapse clauses in OMP have some confusing points
  • Marklogic : Query response time is very high
  • PHP buffered output depending on server setting?
  • Functions in global context
  • Breeze - Deleted Items nav properties bug
  • Projection media query: browser support and workarounds?
  • Using $this when not in object context
  • How do I fake an specific browser client when using Java's Net library?
  • javaw.exe and eclipse startup problems
  • How reduce the height of an mschart by breaking up the y-axis
  • Is possible to count alias result on mysql
  • Check if a string to interpolate provides expected placeholders
  • Perl system calls when running as another user using sudo
  • Deserializing XML into class C#
  • Do create extension work in single-user mode in postgres?
  • Function pointer “assignment from incompatible pointer type” only when using vararg ellipsis
  • R: gsub and capture
  • jqPlot EnhancedLegendRenderer plugin does not toggle series for Pie charts
  • Unanticipated behavior
  • Comma separated Values
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • File upload with ng-file-upload throwing error
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • need help with bizarre java.net.HttpURLConnection behavior
  • python draw pie shapes with colour filled
  • Can't mass-assign protected attributes when import data from csv file
  • How to Embed XSL into XML
  • Unable to use reactive element in my shiny app
  • How to load view controller without button in storyboard?