CODEWITHSUNDEEP
sqlsumsql-server-2016union-all
user9399808
user9399808

Reputation:

SUM of Union ALL? is it possible?

How I can SUM 2 Union all's for a field? Basically, I got 1 table working with 2 rows on it. Now I want to add 3rd row wherein I want to SUM a field in the first row and SUM a field of 2nd row and have the two SUM results appear in the third row, just as a query (not create a new table). I tried UNION but this gives an error.

Date Type                     Orders         TOTAL
13/03 Sales Order             300            15000
13/03 Invoice                 200            10000

-------------I want to add third row to get the total of 1500 and 1000------

(How can I get this value of 25000) as a field?

yes, I have applied union all to get these the 2 rows above. But how about if I want to add the third row? does it mean I have to put another UNION ALL?

Upvotes: 1

Views: 7821

Answers (2)

SKLTFZ
SKLTFZ

Reputation: 950

Try to wrap a select to the dataset and sum the field you wanted.

SELECT SUM(value) as total from(
SELECT 'A2' as field2,'A3' as field3,'A' as name,100 as value 
UNION ALL 
SELECT 'B2' as field2,'B3' as field3,'B' as name,200 as value)
as tbl

UNION ALL the total row ... certainly it is much faster if you can use temp table to store the first result (you can expect it is very slow if you execute it)

SELECT 'A2' as field2,'A3' as field3,'A' as name,100 as value 
UNION ALL 
SELECT 'B2' as field2,'B3' as field3,'B' as name,200 as value
UNION ALL
SELECT 'C1' as field2,'C2' as field3, 'C' as name, SUM(value) as total from (
SELECT 'A2' as field2,'A3' as field3,'A' as name,100 as value 
UNION ALL 
SELECT 'B2' as field2,'B3' as field3,'B' as name,200 as value)
as tbl

IF your query is written in the stored procedure, then you can apply temptable as below

;WITH tmpTable
AS (
SELECT 'A2' as field2,'A3' as field3,'A' as name,100 as value 
UNION ALL 
SELECT 'B2' as field2,'B3' as field3,'B' as name,200 as value
)
SELECT * FROM tmpTable UNION ALL
SELECT 'C1' as field2,'C2' as field3, 'C' as name, SUM(value) as total from tmpTable

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269803

Perhaps this is what you want to do:

select Date, Type, Orders, TOTAL
from
(
    select Date, Type, Orders, TOTAL, 1 AS pos
    from t
    union all
    select NULL, NULL, NULL, sum(total), 2
    from t
) t
order by pos;

Upvotes: 1

Related Questions