Query for Total sum by monthly/year wise by refe c

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query for Total sum by monthly/year wise by refe c

Author

Topic

shagil.a.gopinath
Starting Member

14 Posts

Posted - 2015-02-20 : 07:11:37

Hi

Can anyone please help me to solve the below issue for me, In the Reference_Master table I have Total count based on date for each RefCode. Can I get the total count of each RefCode by monthly wise for each year

Reference_Master

RefCode Date Count
10001 2/18/2007 4
10001 2/19/2007 8
10001 2/20/2007 4
10002 2/18/2007 3
10002 2/19/2007 2
10002 2/20/2007 6
10003 3/18/2007 4
10003 3/19/2007 1
10004 3/20/2007 2
10004 3/18/2007 1
10005 3/19/2007 1
10005 3/20/2007 1
10001 2/18/2008 4
10001 2/19/2008 8
10001 2/20/2008 4
10002 2/18/2008 3
10002 2/19/2008 2
10002 2/20/2008 6
10003 3/18/2008 4
10003 3/19/2008 1
10004 3/20/2008 2
10004 3/18/2008 1
10005 3/19/2008 1
10005 3/20/2008 1

I need the result as below

Result

RefCode Month/Year TotalCount
10001 FEB-2007 16
10002 FEB-2007 11
10003 Mar-2007 7
10004 Mar-2007 3
10005 Mar-2007 2
10001 FEB-2008 16
10002 FEB-2008 11
10003 Mar-2008 7
10004 Mar-2008 3
10005 Mar-2008 2

Thanks & Regards

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-20 : 09:34:12

select RefCode, Date, sum(count) as TotalCount
from Reference_Master
group by RefCode,Date

shagil.a.gopinath
Starting Member

14 Posts

Posted - 2015-02-20 : 14:17:13

Output must be as shown as below, One reference should retrieve the total count of one month (JAN, FEB, March etc)

RefCode Month/Year TotalCount
10001 FEB-2007 16
10002 FEB-2007 11
10003 Mar-2007 7
10004 Mar-2007 3
10005 Mar-2007 2
10001 FEB-2008 16
10002 FEB-2008 11
10003 Mar-2008 7
10004 Mar-2008 3
10005 Mar-2008 2

Thanks & Regards

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-20 : 14:43:53

OK - try this then:

select RefCode, year([date])*100+month([date]), sum([Count]) as TotalCount
from @Reference_Master
group by RefCode,year([date])*100+month([date])

I'll leave the formatting to you!

shagil.a.gopinath
Starting Member

14 Posts

Posted - 2015-02-20 : 16:07:02

Wow, Thats really great. Its worked for me. I never thought of this format.

I have one more query if you dont mind. Suppose if some of the RefCode doesnt have the count in any of the dates, can we retrieve the RefCode which doesnt have the count

Thanks & Regards

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-20 : 17:00:48

Not sure what you mean. YOu only have one table and a refcode/date pair in each row. So there cannot be a refcode that has no count for any date, unless the count can be null. Is that the case?

shagil.a.gopinath
Starting Member

14 Posts

Posted - 2015-02-22 : 03:34:58

Thanks for your response

In some cases One refCode might have no record in one month, So if the record has null value. Will it give the TotalCount as Zero

Thanks & Regards

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-02-22 : 06:56:47

Maybe something like:

select b.RefCode
      ,a.MonthYear
      ,sum(b.[Count]) as TotalCount
  from (select year([date])*100+month([Date]) as MonthYear
          from Reference_Master
         group by year([Date])*100+month([Date])
       ) as a
       left outer join Reference_Master as b
                    on year(b.[Date])*100+month(b.[Date])=a.MonthYear
 group by b,RefCode
         ,a.MonthYear

shagil.a.gopinath
Starting Member

14 Posts

Posted - 2015-03-07 : 06:21:45

Thank you so much :)

Thanks & Regards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources