?
Solved

SQL UNION AGGREGATE FUNCTION

Posted on 2008-02-09
4
Medium Priority
?
1,547 Views
Last Modified: 2010-04-21
I am creating an SQL query which (simplified) looks like

SELECT SUM(quantity) FROM ItemSales
WHERE UnitID = 1
UNION
SELECT SUM(quantity) FROM ProductionLoss
WHERE UnitID = 1

The above would give me a table with 2 lines, one for each select.
Question: How can I get an aggregate result yielding only a single result as the sum of the 2 rows produced.

Is there a way to do this without using temporary tables and stored procedures.

0
Comment
Question by:HLyder
  • 2
  • 2
4 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20858087
here we go:
SELECT SUM(Total) 
FROM ( SELECT SUM(quantity) Total FROM ItemSales
WHERE UnitID = 1
UNION ALL
SELECT SUM(quantity) FROM ProductionLoss
WHERE UnitID = 1
)

Open in new window

0
 

Author Comment

by:HLyder
ID: 20858139
Yes it looks just like what I am looking for but the query is returning an error message indicating incorrect syntax from the [Microsoft ODBC SQL Server Driver] pointing to the last ) in the statement.  
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 20858159
ooooops ...
SELECT SUM(Total) 
FROM ( SELECT SUM(quantity) Total FROM ItemSales
WHERE UnitID = 1
UNION ALL
SELECT SUM(quantity) FROM ProductionLoss
WHERE UnitID = 1
) as subqueryalias

Open in new window

0
 

Author Closing Comment

by:HLyder
ID: 31429519
you rock, thanks
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

599 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