CODEWITHSUNDEEP
mysql
Thiyagu
Thiyagu

Reputation: 786

Mysql union does not sum same values

Below is my sample code for mysql

SELECT ROUND(SUM(final.spend),4) as tot_spend
FROM 
(SELECT 1 as spend FROM `tab1` as tds WHERE 1
UNION
SELECT 1 as spend  FROM `tab2` as obm  WHERE 1
) as final

For better understanding I replace the column value as 1 in query, because value from both tables are same,If run above query I get result 1, it does not sum the value, if I change any one table value for Eg. change tab2 value as 2 and run means it shows 3, for my understanding if the value from both tables are same means SUM wont work , differ means its SUM the value from the tables, is the default one or my understanding is wrong? Anyone help to solve my issue, I need to sum any value(same or differ) from both table.

Upvotes: 1

Views: 324

Answers (1)

waka
waka

Reputation: 3407

The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL:

SELECT ROUND(SUM(final.spend),4) as tot_spend
FROM 
(SELECT 1 as spend FROM `tab1` as tds WHERE 1
UNION ALL
SELECT 1 as spend  FROM `tab2` as obm  WHERE 1
) as final

This will give you the correct sum.

Upvotes: 4

Related Questions