Sum is doubling up my figures

1

Hi, could someone help me out with this please.

I have a single table which I am trying to reference twice without doubling up the figures. It looks like this..

Name - TranDate - Value
John 01-Oct-18 10.00
John 02-Nov-18 10.00
John 03-Nov-18 10.00

and I would like a report that shows it like this

Name ValueNovemberOnly - ValueAllTime
John 20.00 30.00

This is what I've tried..

select A.Name, sum(A.Value) as ValueAllTime
from MyTable A
left join (select * from MyTable where TranDate between '2018/11/01' and '2018/11/30') B on A.Name=B.Name
group by A.Name

but my values are way bigger than they should be. Please help!

2

Something like this?? this will query the whole table

declare @t table (Name varchar(10),  TranDate date, Value numeric(10,2))

insert into @t values
('John','01-Oct-18', 10.00),
('John','02-Nov-18', 10.00),
('John','03-Nov-18', 10.00),
('Mike','01-Oct-18', 10.00),
('Mike','02-Nov-18', 15.00),
('Mike','03-Nov-18', 20.00)

select Name, sum(case when trandate  between '2018/11/01' and '2018/11/30' then value else 0 end) MTD,
  Sum(value) YTD
from @t
group by name
1 Like
3

Mike, you are a legend!! Thank you very much, works perfect!