Sum Query - SQL Server Forums

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

Sum Query

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-22 : 11:33:14

Hi guys #

Sorry to bother you again

this is my query

SELECT [fdmsaccountno]
,[hst_date_processed]
,[MSC]
,[Refund Transaction Charge]
,[PCI]
,[Joining Fee]
,[Other]
FROM [Rm_Book_New].[dbo].[tmpbd1]
Where fdmsaccountno = '878020388889'

however i need to work out the total msc for each year , also i would like to know the difference between 2011 and 2012

In the eg below 2011 figure is 427.44, 2012 is 1062.47 that is a difference of £635.03

Can anyone help me with this ?

fdmsaccountno hst_date_processed MSC
878020388889 2010-01-01 338.11
878020388889 2011-02-01 427.44
878020388889 2012-03-01 1062.47

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-22 : 11:47:44

[code]
;With Temp
AS
(
SELECT fdmsaccountno,YEAR(hst_date_processed) AS YrValue,SUM(MSC) AS Total,
ROW_NUMBER() OVER (PARTITION BY fdmsaccountno ORDER BY YEAR(hst_date_processed)) AS Seq
FROM [Rm_Book_New].[dbo].[tmpbd1]
GROUP BY fdmsaccountno,YEAR(hst_date_processed)
)

SELECT t1.*,t1.Total-COALESCE(t2.Total,0) AS Diff
FROM Temp t1
LEFT JOIN Temp t2
ON t1.fdmsaccountno = t2.fdmsaccountno
AND t1.Seq=t2.Seq+1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

theboyholty
Posting Yak Master

226 Posts

Posted - 2012-08-22 : 11:50:09

Maybe this will get you started.

SELECT [fdmsaccountno]
,[hst_date_processed]
,[MSC]
,ys.SumMSC
,[Refund Transaction Charge]
,[PCI]
,[Joining Fee]
,[Other]
FROM  [Rm_Book_New].[dbo].[tmpbd1] t
INNER JOIN (
	select DATEPART(YEAR,[hst_date_processed]) as YearNo,SUM(MSC) as SumMSC 
	from [Rm_Book_New].[dbo].[tmpbd1] 
	GROUP BY DATEPART(YEAR,[hst_date_processed])
	)ys on ys.YearNo=DATEPART(YEAR,t.[hst_date_processed])
Where fdmsaccountno = '878020388889'

Here I've added a subquery to calculate the MSC total for each year (using the [hst_date_processed] field. I've joined that back to the main table based on the YEAR datepart of that datetime field, then I've included the SumMSC field in your select list.
If you want to add an additional grouping and get annual totals per year per[fdmsaccountno] for example, then add that field as an additional grouping into the subquery and join on that field too.
Its probably not perfect because there's very little information to work with but as a general pointer, it should get you started.

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk A Bury FC supporters website and forum

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-22 : 12:13:18

quote:
Originally posted by theboyholty

Maybe this will get you started.

SELECT [fdmsaccountno]
,[hst_date_processed]
,[MSC]
,ys.SumMSC
,[Refund Transaction Charge]
,[PCI]
,[Joining Fee]
,[Other]
FROM [Rm_Book_New].[dbo].[tmpbd1] t
INNER JOIN (
select DATEPART(YEAR,[hst_date_processed]) as YearNo,SUM(MSC) as SumMSC
from [Rm_Book_New].[dbo].[tmpbd1]
GROUP BY DATEPART(YEAR,[hst_date_processed])
)ys on ys.YearNo=DATEPART(YEAR,t.[hst_date_processed])
Where fdmsaccountno = '878020388889'

Here I've added a subquery to calculate the MSC total for each year (using the [hst_date_processed] field. I've joined that back to the main table based on the YEAR datepart of that datetime field, then I've included the SumMSC field in your select list.
If you want to add an additional grouping and get annual totals per year per[fdmsaccountno] for example, then add that field as an additional grouping into the subquery and join on that field too.
Its probably not perfect because there's very little information to work with but as a general pointer, it should get you started.

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk A Bury FC supporters website and forum

you need to include accountno field also in derived table else it will return entire total MSC for the year regardless of accountno value which is wrong

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-22 : 14:08:44

HI GUYS

PLEASE FIND ATTACHED IMAGE

THIS SHOULD CLEAR ANY ISSUES OR DOUBTS

http://i48.tinypic.com/2njgd5h.jpg

LOOKING FORWARD TO YOUR RESPONSE

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-22 : 14:41:50

[code]
;With Temp
AS
(
SELECT fdmsaccountno,YEAR(hst_date_processed) AS YrValue,SUM(MSC) AS Total,
ROW_NUMBER() OVER (PARTITION BY fdmsaccountno ORDER BY YEAR(hst_date_processed)) AS Seq
FROM [Rm_Book_New].[dbo].[tmpbd1]
GROUP BY fdmsaccountno,YEAR(hst_date_processed)
)

SELECT t1.fdmsaccountno,t1.Total,t2.Total,t1.Total-COALESCE(t2.Total,0) AS Diff
FROM Temp t1
LEFT JOIN Temp t2
ON t1.fdmsaccountno = t2.fdmsaccountno
AND t1.Seq=t2.Seq+1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-22 : 14:49:14

Hey visakhm

thank you for your post
will that query provide the layout which i need ?

Also i dropped you a msg, if you could have a look and let me know, id appreciate it :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-22 : 14:52:13

quote:
Originally posted by masond

Hey visakhm

thank you for your post
will that query provide the layout which i need ?

Also i dropped you a msg, if you could have a look and let me know, id appreciate it :)

whats the layout you provided

also please post it here rather than sending me msg for quick response

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-22 : 14:59:45

HI visakh16

if you click on the link provided above, it should provide you to a screen print.

The layout i need will be in yellow

The question i posted to you, is that i got some projects comming my way, and as i am new to sql, i will struggle with the queries/criteria they need.
I know its cheeky asking, but as you helped me out on this forum alot ,i was wondering would you mind helping me out and i paid you for your assistance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-22 : 15:03:35

quote:
Originally posted by masond

HI visakh16

if you click on the link provided above, it should provide you to a screen print.

The layout i need will be in yellow

The question i posted to you, is that i got some projects comming my way, and as i am new to sql, i will struggle with the queries/criteria they need.
I know its cheeky asking, but as you helped me out on this forum alot ,i was wondering would you mind helping me out and i paid you for your assistance

ok..for that question is will there be always only 2 years worth of data? or is it variable?

Yep..I'll be happy to assist. will reply to your message later as i cant access email from here

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-22 : 15:08:10

hi visakh16

there be data going back to 2006

However i am only interested in current year and previous year
So if you run the report next year it will be
2013 and 2012 and difference between the two

2014 and 2013 and difference between the two
etc etc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-22 : 15:11:42

quote:
Originally posted by masond

hi visakh16

there be data going back to 2006

However i am only interested in current year and previous year
So if you run the report next year it will be
2013 and 2012 and difference between the two

2014 and 2013 and difference between the two
etc etc

if its always two consecutive years then my last suggestion would be enough

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-22 : 15:18:15

HI visakh16, i will try and using this query when i am back at work

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-22 : 15:23:11

ok...let me know if you need more help

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-22 : 15:24:55

hi Visakh16

I emailed your gmail account, so when you get a chance have a little look at that. and if your interested we can hopefully discuss in more detail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-22 : 15:37:27

quote:
Originally posted by masond

hi Visakh16

I emailed your gmail account, so when you get a chance have a little look at that. and if your interested we can hopefully discuss in more detail

Sure

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources