SUM() function issue ?

Question

I have 2 tables sales and overrides ?

Query-

Select SUM(S.GrossSal) from sales S where S.cmonth=1 and S.cYear=2012

-- output 100

select SUM(O.GrossOverrides) from Overrides O where O.cMonth=1 and O.cYear=2012

-- output 0

select SUM(S.GrossSal) + SUM(O.GrossOverrides) from sales S inner join Overrides O

on S.storeid=O.Storeid

where S.cMonth=1 and S.cYear=2012

-- Output 250

Which approach i should follow!

Answer 1

All are looking totally different queries and business logic. Please let us know what do you try to acheive, would be able to help better.

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 2

How to get sum of two columns, but two columns are available in two different tables.

Answer 3

They are different query in themselves 

With the first query you can guarantee that you will get SUM as 100 or less when you introduce StoreID also 

With the second query there exist no record that matches your where clause 

With thired query you are joining both the tables on StoreID and you are including the same where clause as with the fisrst query. To get consistent 

SUm include the same where clause from second query also

---

Thanks and regards, Rishabh K

Answer 4

Try something like below:(Not tested)

Select sum(A.Sal) From

(Select SUM(S.GrossSal) sal from sales S where S.cmonth=1 and S.cYear=2012

Union All

select SUM(O.GrossOverrides) sal from Overrides O where O.cMonth=1 and O.cYear=2012

) A

Or

select SUM(S.GrossSal) + SUM(O.GrossOverrides) from sales S inner join Overrides O

on S.storeid=O.Storeid and S.cmonth=O.cMonth and S.cYear=O.cYear

where S.cMonth=1 and S.cYear=2012

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 5

This query gives me wrong output!

Answer 6

Can you show us the table staructure and sample data and your desired output plz.

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 7

I am giving you mirror of my requirement as i cannot paste data-

Please find sample-

create table #temp1(id int,sal int)
insert into #temp1 values(1,10)
insert into #temp1 values(2,20)
insert into #temp1 values(3,30)

create table #temp2(id int,sal int)
insert into #temp2 values(1,10)
insert into #temp2 values(1,NULL)
insert into #temp2 values(2,30)

select * from #temp1
select * from #temp2
select SUM(sal) from #temp1 where id=1
select SUM(sal) from #temp2 where id=1

select * from #temp1
select * from #temp2
select SUM(a.sal) as Ist,SUM(b.sal) as IInd, SUM(a.sal) + coalesce(SUM(b.sal),0) as TotSal from #temp1 a inner join #temp2 b
on a.id=b.id
where a.id=1

In the final output --

Why it is 30 in TotSal column.It should be 20.

Answer 8

When you join these tywo table, you have two rows returning for id=1. That is the reason you are getting first result as 20. Hence the filanl total will be 20+10=30.

Try the below:

Select sum(A.Sal) From
(Select SUM(S.sal) sal from #temp1 S where s.id=1 
Union All
select SUM(O.sal) sal from #temp2 O where O.id=1
) A

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 9

Here in Temp2 table - you have two rows for Id = 1 (one with sal = 10 and another with sal = NULL) and for that reason the rows from temp1 are getting doubled.

So you are seeing Ist sum as 20 instead of 10. Verify the below query added with NOT NULL conditions on Sal.

select SUM(a.sal) as Ist, SUM(b.sal) as IInd, SUM(a.sal) + coalesce(SUM(b.sal),0) as TotSal 
 from #temp1 a inner join #temp2 b
 on a.id=b.id
 where a.id=1 and (b.sal IS NOT NULL and a.sal IS NOT NULL)

Thanks!

Answer 10

Try:

;with cteSales AS (Select SUM(S.GrossSal) as GrossSal, cMonth, cYear, StoreId 
from sales S where S.cmonth=1 and S.cYear=2012
GROUP BY cYear, cMonth, StoreId),
cteOverrides AS (
select SUM(O.GrossOverrides) as GrossOverrides, cYear, cMonth, StoreId 
from Overrides O
 where O.cMonth=1 and O.cYear=2012
GROUP BY cYear, cMonth, StoreId)

select coalesce(c1.GrossSal,0) + coalesce(c2.GrossOverrides,0)
AS [Total] from cteSales c1 FULL JOIN cteOverrides c2
ON c1.cYear = c2.cYear and c1.cMonth = c2.cMonth AND c1.StoreId = c2.StoreId

Take a look at this blog post

Aggregates with multiple tables

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 11

giving error on below line--

ON c1.cYear = c2.cYear and c1.cMonth = c2.cMonth

Answer 12

I think it is due to data type mismatch.

---

Many Thanks & Best Regards, Hua Min

Answer 13

I think it is due to data type mismatch.

---

Many Thanks & Best Regards, Hua Min

its bcz of missing fields for month and year for CTE. Do not put the join condition.

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 14

Take another look at that solution, I added these columns.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 15

Take another look at that solution, I added these columns.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Hi Noami, I guess storeid is a missing factor again right? please correct me if am wrong or overlooked something.

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 16

Good point. I'll add it also.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog