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
Go to Top of Page

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



Go to Top of Page

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!
Go to Top of Page

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



Go to Top of Page

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?
Go to Top of Page

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



Go to Top of Page

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
Go to Top of Page

shagil.a.gopinath
Starting Member

14 Posts

Posted - 2015-03-07 : 06:21:45
Thank you so much :)



Thanks & Regards



Go to Top of Page
   

- Advertisement -