<div>
here we go:<br />

<pre><code id="code-37264">SELECT SUM(Total) 
FROM ( SELECT SUM(quantity) Total FROM ItemSales
WHERE UnitID = 1
UNION ALL
SELECT SUM(quantity) FROM ProductionLoss
WHERE UnitID = 1
)
</code></pre>
</div>


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
)


<div>
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. &nbsp;
</div>


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.  

<div>
<div class="content wysiwyg-content">
I am creating an SQL query which (simplified) looks like<br />
<br />
SELECT SUM(quantity) FROM ItemSales<br />
WHERE UnitID = 1<br />
UNION<br />
SELECT SUM(quantity) FROM ProductionLoss<br />
WHERE UnitID = 1<br />
<br />
The above would give me a table with 2 lines, one for each select. <br />
Question: How can I get an aggregate result yielding only a single result as the sum of the 2 rows produced. <br />
<br />
Is there a way to do this without using temporary tables and stored procedures.<br />
<br />

</div>
</div>


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.



SQL UNION AGGREGATE FUNCTION

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.