query help 1

[swoodring]

I'm trying to calculate the gross margin and gross margin %. I have the gross margin calcualting. I'm now trying to calculate the %. It keeps coming back with 0 which doesn't make sense to me. Basically I'm taking the sum of the gross margin and dividing it by the sum of the sales. Since in the results I have values in both the sales and what I calculate to be gross margin I do not expect 0. Based on the results I would expect .42 What am I doing wrong.

Here's the results:
net_sales cost_Of_sales gross_margin gross_margin_percent
----------- ------------- ------------ --------------------
6315935 3674110 2641825 0

(1 row(s) affected)

Here's the query:

SELECT
sum(dbo.WeeklySalesHistory.SalesRetailVATInclusive) as net_sales,
sum(dbo.WeeklySalesHistory.CostofSales)as cost_Of_sales,
sum(dbo.WeeklySalesHistory.SalesRetailVATInclusive - dbo.WeeklySalesHistory.CostofSales) as gross_margin,
sum(dbo.WeeklySalesHistory.SalesRetailVATInclusive - dbo.WeeklySalesHistory.CostofSales)/sum(dbo.WeeklySalesHistory.SalesRetailVATInclusive)as gross_margin_percent
FROM
dbo.WeeklySalesHistory
WHERE
(
( (dbo.WeeklySalesHistory.WeekEndingDate
IN(SELECT DISTINCT(weeks.isodate)
FROM dbo.Weeks , dbo.MonthEndingDates JOIN dbo.YearParameters
ON dbo.MonthEndingDates.MonthStartDate >= dbo.YearParameters.Start_Date
and dbo.MonthEndingDates.MonthEndDate <=dbo.YearParameters.End_Date and
dateadd(dd, -1, getdate()) >= dbo.YearParameters.Start_Date and
dateadd(dd, -1, getdate()) <= dbo.YearParameters.End_Date
WHERE (dbo.weeks.Isodate >=dbo.MonthEndingDates.MonthStartDate
AND dbo.weeks.Isodate <=dbo.MonthEndingDates.MonthEndDate
And dbo.MonthEndingDates.MonthNum = 1 ) )) ))

 

[tlbroadbent]

It appears that all numbners are integers so the division will result in 0. Multiply the numerator by 100 to return percent as an integer.

SELECT
sum(dbo.WeeklySalesHistory.SalesRetailVATInclusive) as net_sales,
sum(dbo.WeeklySalesHistory.CostofSales)as cost_Of_sales,
sum(dbo.WeeklySalesHistory.SalesRetailVATInclusive - dbo.WeeklySalesHistory.CostofSales) as gross_margin,
sum(dbo.WeeklySalesHistory.SalesRetailVATInclusive - dbo.WeeklySalesHistory.CostofSales) * 100 /sum(dbo.WeeklySalesHistory.SalesRetailVATInclusive)as gross_margin_percent
FROM
dbo.WeeklySalesHistory
WHERE

You can also convert one or more of the of the numbers to Decimal using the CAST or CONVERT function.

cast(sum(dbo.WeeklySalesHistory.SalesRetailVATInclusive - dbo.WeeklySalesHistory.CostofSales) As Decimal(12,4))/cast(sum(dbo.WeeklySalesHistory.SalesRetailVATInclusive) As Decimal(12,4)) as gross_margin_percent
Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[rajeevnandanmishra]

Hi swoodring,

Terry is saying correct. But it seems in sql server that if you only have integers in calculation it will return you integer only. That means multiplying the numerator with 100 will give you integer value of percentage.

If you want the accurate value then you should mulitiply by 100.00
Mulityplying by a decimal converts the whole data to decimal.