locked
SUM() function issue ? RRS feed

  • 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!

    Thursday, September 13, 2012 8:58 AM

Answers

  • 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

    • Marked as answer by Maggy111 Tuesday, September 18, 2012 5:05 PM
    Thursday, September 13, 2012 9:30 AM
  • 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!

    • Marked as answer by Maggy111 Tuesday, September 18, 2012 5:05 PM
    Thursday, September 13, 2012 9:34 AM
  • 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!

    • Marked as answer by Maggy111 Tuesday, September 18, 2012 5:05 PM
    Thursday, September 13, 2012 10:18 AM
  • 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!
    • Marked as answer by Maggy111 Tuesday, September 18, 2012 5:06 PM
    Thursday, September 13, 2012 10:21 AM
  • 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



    • Proposed as answer by Vishnu Dalwadi Friday, September 14, 2012 11:02 AM
    • Edited by Naomi N Friday, September 14, 2012 12:35 PM
    • Marked as answer by Maggy111 Tuesday, September 18, 2012 5:06 PM
    Thursday, September 13, 2012 12:46 PM
  • 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

    • Marked as answer by Maggy111 Tuesday, September 18, 2012 5:06 PM
    Friday, September 14, 2012 12:25 PM

All replies

  • 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!

    Thursday, September 13, 2012 9:02 AM
  • How to get sum of two columns, but two columns are available in two different tables.

    Thursday, September 13, 2012 9:26 AM
  • 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

    • Marked as answer by Maggy111 Tuesday, September 18, 2012 5:05 PM
    Thursday, September 13, 2012 9:30 AM
  • 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!

    • Marked as answer by Maggy111 Tuesday, September 18, 2012 5:05 PM
    Thursday, September 13, 2012 9:34 AM
  • This query gives me wrong output!
    Thursday, September 13, 2012 9:44 AM
  • 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!

    Thursday, September 13, 2012 9:53 AM
  • 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.


    • Edited by Maggy111 Thursday, September 13, 2012 10:09 AM
    Thursday, September 13, 2012 10:09 AM
  • 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!

    • Marked as answer by Maggy111 Tuesday, September 18, 2012 5:05 PM
    Thursday, September 13, 2012 10:18 AM
  • 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!
    • Marked as answer by Maggy111 Tuesday, September 18, 2012 5:06 PM
    Thursday, September 13, 2012 10:21 AM
  • 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



    • Proposed as answer by Vishnu Dalwadi Friday, September 14, 2012 11:02 AM
    • Edited by Naomi N Friday, September 14, 2012 12:35 PM
    • Marked as answer by Maggy111 Tuesday, September 18, 2012 5:06 PM
    Thursday, September 13, 2012 12:46 PM
  • giving error on below line--

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

    Friday, September 14, 2012 9:59 AM
  • I think it is due to data type mismatch.

    Many Thanks & Best Regards, Hua Min

    Friday, September 14, 2012 10:04 AM
  • 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!

    Friday, September 14, 2012 10:10 AM
  • 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

    • Marked as answer by Maggy111 Tuesday, September 18, 2012 5:06 PM
    Friday, September 14, 2012 12:25 PM
  • 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!


    • Edited by SQLZealots Friday, September 14, 2012 12:33 PM
    Friday, September 14, 2012 12:33 PM
  • Good point. I'll add it also.

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


    My blog

    Friday, September 14, 2012 12:34 PM