0

I have 3 independent querys that I would like to sum the values obtained in each query. These querys obtain a count value for each day of a month:

query one

day|val| 
1  | 2 
2  | 6 
3  | 5* 
4  | 2 
5  | 7

query 2

day|val|
2  | 1
3  | 5*
10 | 5
11 | 2
12 | 7

I am using the instruction:

select day, SUM(val) 
from query1 UNION query2
group by day

I found out that when a value is repeated instead of sum it would use one of the repeated values, for axample the days number 3 both have value 5. the instruction would give onlye value 5 instead of 10.

This is supposed to give me a report of total of values per day in a month but the report is wrong because of this problem caused by UNION How can i fix it?

gla3dr
  • 2,179
  • 16
  • 29
Marcos CC
  • 3
  • 3
  • Check difference between `UNION` (which removes duplicates) and `UNION ALL` (which not remove duplicates) – Bogdan Bogdanov Aug 17 '15 at 16:44
  • Thanks. I didn´t know thera was a UNION ALL – Marcos CC Aug 17 '15 at 16:46
  • It seems like it is actually a duplicate question, maybe I Just dind't know how to find the answer. Now what should I do? – Marcos CC Aug 17 '15 at 16:55
  • Marcos, if you feel that the question is sufficiently addressed there is no need to do anything! Read [this](http://stackoverflow.com/help/duplicates) for a little more information on what StackOverflow considers to be a duplicate, and why we mark it the way we do. – AHiggins Aug 17 '15 at 17:01

2 Answers2

1

You should do

select day, sum(val) 
from (
select day,val from table1
union all
select day,val from table2) t
group by day;

union all preserves duplicates.

Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58
1

union removes duplicates, as you noticed. Instead, you should use union all:

SELECT   day, SUM(val) 
FROM     query1 UNION ALL query2
GOURP BY day
Mureinik
  • 297,002
  • 52
  • 306
  • 350