Division And 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

Division And Sum Query

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-07-04 : 06:06:05

Hey guys

I hoping you will be able to help me

Aim – Create a new column called “% of Sales” . The calculation for this column is a division of the month sales (hst_date_processed)via the total sum of every month.

would appreciate any help available

This is my query
Declare @Rolling12tempFROM varchar(10)
Declare @Rolling12tempTO varchar(10)
set @Rolling12tempFROM = (select dateadd(MM,-11,max(Period))
from [FDMS].[dbo].[Fact_ProcessingCost])
set @Rolling12tempTO = (select (max(Period))
from [FDMS].[dbo].[Fact_ProcessingCost])

--Calculating every Parent Sales For Rolling 12 By month --
SELECT
Dim_Outlet.ParentID,
hst_date_processed as Months,
Sum(Fact_Financial_History.hst_sales_amt) as Sales
--Into #fin
FROM Fact_Financial_History
INNER JOIN Dim_Outlet ON Fact_Financial_History.hst_merchnum = Dim_Outlet.FDMSAccountNo_First9
where (hst_date_processed BETWEEN @Rolling12tempFROM and @Rolling12tempTO)
and ParentID = '878177270880'
group by ParentID, hst_date_processed
having SUM (hst_sales_amt) > 0
Order by Months desc

Which produces the following

ParentID Months Sales
878177270880 01/05/2013 47101314
878177270880 01/04/2013 49201040
878177270880 01/03/2013 47103607
878177270880 01/02/2013 41352269
878177270880 01/01/2013 45916620
878177270880 01/12/2012 41420931
878177270880 01/11/2012 44688557
878177270880 01/10/2012 51193302
878177270880 01/09/2012 46170188
878177270880 01/08/2012 54335833
878177270880 01/07/2012 55296355
878177270880 01/06/2012 46528750

Expected result

ParentID Months Sales % of Sales
878177270880 01/05/2013 47101314 8%
878177270880 01/04/2013 49201040 9%
878177270880 01/03/2013 47103607 8%
878177270880 01/02/2013 41352269 7%
878177270880 01/01/2013 45916620 8%
878177270880 01/12/2012 41420931 7%
878177270880 01/11/2012 44688557 8%
878177270880 01/10/2012 51193302 9%
878177270880 01/09/2012 46170188 8%
878177270880 01/08/2012 54335833 10%
878177270880 01/07/2012 55296355 10%
878177270880 01/06/2012 46528750 8%

Total 570308766 100.00%

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-04 : 07:17:42

[code]
SELECT *,
Sales/Sum(Sales) OVER () AS [%Sales]
FROM
(
SELECT
Dim_Outlet.ParentID,
hst_date_processed as Months,
Sum(Fact_Financial_History.hst_sales_amt) as Sales
--Into #fin
FROM Fact_Financial_History
INNER JOIN Dim_Outlet ON Fact_Financial_History.hst_merchnum = Dim_Outlet.FDMSAccountNo_First9
where (hst_date_processed BETWEEN @Rolling12tempFROM and @Rolling12tempTO)
and ParentID = '878177270880'
group by ParentID, hst_date_processed
having SUM (hst_sales_amt) > 0
)t
Order by [Months] desc
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-07-04 : 08:12:43

hi visakh16

Thank you for your post

If i want to produce an other column which is called "%Index"
The calculation for this column is a average of the month sales (hst_date_processed)via the total sum of every month
how would i do that ?

i have tried adapting your query, but i am getting the same value being populated within my dataset

SELECT *,
Sales/Sum(Sales) OVER () AS [%Sales],
AVG(Sales) OVER () AS [Index]
FROM
(
SELECT
Dim_Outlet.ParentID,
hst_date_processed as Months,
Sum(Fact_Financial_History.hst_sales_amt) as Sales
--Into #fin
FROM Fact_Financial_History
INNER JOIN Dim_Outlet ON Fact_Financial_History.hst_merchnum = Dim_Outlet.FDMSAccountNo_First9
where (hst_date_processed BETWEEN @Rolling12tempFROM and @Rolling12tempTO)
and ParentID = '878177270880'
group by ParentID, hst_date_processed
having SUM (hst_sales_amt) > 0
)t
Order by [Months] desc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-04 : 08:48:04

[code]
SELECT *,
Sales/Sum(Sales) OVER () AS [%Sales],
AvgSales/Sum(Sales) OVER () AS [%Index]
FROM
(
SELECT
Dim_Outlet.ParentID,
hst_date_processed as Months,
Sum(Fact_Financial_History.hst_sales_amt) as Sales,
Avg(Fact_Financial_History.hst_sales_amt * 1.0) as AvgSales
--Into #fin
FROM Fact_Financial_History
INNER JOIN Dim_Outlet ON Fact_Financial_History.hst_merchnum = Dim_Outlet.FDMSAccountNo_First9
where (hst_date_processed BETWEEN @Rolling12tempFROM and @Rolling12tempTO)
and ParentID = '878177270880'
group by ParentID, hst_date_processed
having SUM (hst_sales_amt) > 0
)t
Order by [Months] desc
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-07-04 : 09:10:16

HI Visakh16

Thank you for your response, However i am not getting the required results which i expect

By using your logic i am producing the following

ParentID Months Sales AvgSales %Sales %Index
8.78177E+11 01/05/2013 47101313.87 37590.8331 8.25% 0.01%
8.78177E+11 01/04/2013 49201040.47 38924.87379 8.62% 0.01%
8.78177E+11 01/03/2013 47103607.48 38832.32274 8.25% 0.01%
8.78177E+11 01/02/2013 41352268.8 35163.49388 7.25% 0.01%
8.78177E+11 01/01/2013 45916619.75 37947.61963 8.05% 0.01%
8.78177E+11 01/12/2012 41420931.15 34719.97582 7.26% 0.01%
8.78177E+11 01/11/2012 44688557.3 36010.11869 7.83% 0.01%
8.78177E+11 01/10/2012 51193301.79 41586.76019 8.97% 0.01%
8.78177E+11 01/09/2012 46170187.7 38668.49891 8.09% 0.01%
8.78177E+11 01/08/2012 54335833.06 43192.23614 9.52% 0.01%
8.78177E+11 01/07/2012 55296355.01 43132.88222 9.69% 0.01%
8.78177E+11 01/06/2012 46528749.85 37889.86144 8.15% 0.01%

What i should be getting is the following

ParentID Months Sales AvgSales %Sales %Index
8.78177E+11 01/05/2013 47101313.87 37590.8331 8.25% 99.11%
8.78177E+11 01/04/2013 49201040.47 38924.87379 8.62% 103.53%
8.78177E+11 01/03/2013 47103607.48 38832.32274 8.25% 99.11%
8.78177E+11 01/02/2013 41352268.8 35163.49388 7.25% 87.01%
8.78177E+11 01/01/2013 45916619.75 37947.61963 8.05% 96.61%
8.78177E+11 01/12/2012 41420931.15 34719.97582 7.26% 87.15%
8.78177E+11 01/11/2012 44688557.3 36010.11869 7.83% 94.03%
8.78177E+11 01/10/2012 51193301.79 41586.76019 8.97% 107.72%
8.78177E+11 01/09/2012 46170187.7 38668.49891 8.09% 97.15%
8.78177E+11 01/08/2012 54335833.06 43192.23614 9.52% 114.33%
8.78177E+11 01/07/2012 55296355.01 43132.88222 9.69% 116.35%
8.78177E+11 01/06/2012 46528749.85 37889.86144 8.15% 97.90%

To produce the %index Column i need to look at the indvidual month sales, and then work out a % average from the total sales

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-04 : 09:44:35

quote:
Originally posted by masond

If i want to produce an other column which is called "%Index"
The calculation for this column is a average of the month sales (hst_date_processed)via the total sum of every month

Do you mean this:
[CODE]

SELECT *,
Sales/Sum(Sales) OVER () AS [%Sales],
AvgSales*Months*100/Sum(Sales) OVER () AS [%Index]FROM
(
SELECT
Dim_Outlet.ParentID,
hst_date_processed as Months,
Sum(Fact_Financial_History.hst_sales_amt) as Sales,
Avg(Fact_Financial_History.hst_sales_amt * 1.0) as AvgSales
--Into #fin
FROM Fact_Financial_History
INNER JOIN Dim_Outlet ON Fact_Financial_History.hst_merchnum = Dim_Outlet.FDMSAccountNo_First9
where (hst_date_processed BETWEEN @Rolling12tempFROM and @Rolling12tempTO)
and ParentID = '878177270880'
group by ParentID, hst_date_processed
having SUM (hst_sales_amt) > 0
)t
Order by [Months] desc
[/CODE]

Can you explain in mathematical terms what "%Index" column should be?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources