Using Division in a SP - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Using Division in a SP

Author

Topic

Meltdown
Starting Member

37 Posts

Posted - 2006-01-28 : 19:01:36

Hi everyone, I have the following T-SQL code



CREATE PROCEDURE dbo.OrdersGrouped 
AS
BEGIN
 
	SET NOCOUNT ON;

SELECT tblReps.Name, tblReps.Active,
	 Sum(tblOrders.Prod_Price) AS 'Total Order Value',
	 Sum(tblOrders.AST_Value) AS 'Total AST',
         Sum(tblOrders.Design_Time) AS 'Total Design Time'
	 Count(tblOrders.OrderNo) AS 'Number of Orders',
	 
FROM tblReps INNER JOIN tblOrders ON tblReps.Code = tblOrders.Rep_Code
GROUP BY tblReps.Name, tblReps.Active;

END
GO

I need to have another column of data called Page Rate which is calculated by dividing 'Total Order Value'/'Number of Orders' AS Page Rate. Can anyone tell me how to achieve this in a SP?

Thanks for any help

Meltdown
Starting Member

37 Posts

Posted - 2006-01-28 : 19:50:08

sorry,ignore

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-28 : 20:05:53

>> RETURN @TotalOrders
use OUTPUT parameter to return the value

----------------------------------
'KH'
I come I saw I post

Meltdown
Starting Member

37 Posts

Posted - 2006-01-28 : 20:09:33

Sorry khtan, I confused the issue with my second post, which I've now deleted, I still need a solution, if possible.

Thanks

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-28 : 23:23:08

Calculate it this way.


case
	-- Prevent divide by zero error
when 	Count(tblOrders.OrderNo) = 0
	-- Return null if divisor is zero
then 	null
else
	-- Convert to decimal to before dividing
	-- to be able to calculate to 2 decimal places
	-- Round result to 2 decimal places
	convert(decimal(10,2),round(
	convert(decimal(15,5),Sum(tblOrders.Prod_Price)) /
	convert(decimal(15,5),Count(tblOrders.OrderNo))
	,2))
end as [Page Rate]

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2006-01-29 : 05:00:18

Or wrap it into a subselect:


CREATE PROCEDURE dbo.OrdersGrouped 
AS
BEGIN
 
SET NOCOUNT ON

SELECT *, [Total Order Value] / NullIf(Number of Orders, 0) as [Page Rate]
FROM
(
	SELECT tblReps.Name, tblReps.Active,
		 Sum(tblOrders.Prod_Price) AS 'Total Order Value',
		 Sum(tblOrders.AST_Value) AS 'Total AST',
	         Sum(tblOrders.Design_Time) AS 'Total Design Time'
		 Count(tblOrders.OrderNo) AS 'Number of Orders',
		 
	FROM tblReps INNER JOIN tblOrders ON tblReps.Code = tblOrders.Rep_Code
	GROUP BY tblReps.Name, tblReps.Active;
) AS X
END
GO

Couple of observations:

I would suggest using [MyAliasName] instead of 'MyAliasName' to avoid SQL Server accidentally interpreting the AliasName as a string if you miss, say, a comma in the syntax somewhere.

I would prefix the tables etc. with "dbo." so that SQL doesn't have to check if a version of the table exists owned-by the current user.

And you ought not to use "SELECT *" as I have done in this example!!!

Kristen

Meltdown
Starting Member

37 Posts

Posted - 2006-01-29 : 07:33:37

Thanks Michael and Kristen for the replies and tips. I have it working now, but I have another question. I would now like to add a percentage column beside the 'Total Order Value' column. This column would indicate what percentage of the overall total that 'Total Order Value' represents.

My Code:



ALTER PROCEDURE dbo.OrdersGrouped 
AS
BEGIN
 
SET NOCOUNT ON

SELECT 
[Rep Name],
[Active Rep],
[Total Order Value],
[Total AST],
[Total Design Time],
[Number of Orders],
[Total Order Value] / NullIf([Number of Orders], 0) as [Page Rate]
FROM
(
	SELECT 
         dbo.tblReps.Name AS [Rep Name], 
         dbo.tblReps.Active AS [Active Rep],
		 Sum(dbo.tblOrders.Prod_Price) AS [Total Order Value],
		 Sum(dbo.tblOrders.AST_Value) AS [Total AST],
	     Sum(dbo.tblOrders.Design_Time) AS [Total Design Time],
		 Count(dbo.tblOrders.OrderNo) AS [Number of Orders] 
    FROM dbo.tblReps INNER JOIN dbo.tblOrders ON dbo.tblReps.Code = dbo.tblOrders.Rep_Code
		GROUP BY dbo.tblReps.Name, dbo.tblReps.Active
) AS X
END

Thanks for any help

Kristen
Test

22859 Posts

Posted - 2006-01-29 : 08:23:07

In that case I think I'd pull the inner select into a temporary table first, use that to work out the "grand total", and then report from the temporary table and calculate the Percentage using the stored "grand total" value.


DECLARE @MyGrandTotal money
SELECT 
	dbo.tblReps.Name AS [Rep Name], 
	dbo.tblReps.Active AS [Active Rep],
	Sum(dbo.tblOrders.Prod_Price) AS [Total Order Value],
	Sum(dbo.tblOrders.AST_Value) AS [Total AST],
	Sum(dbo.tblOrders.Design_Time) AS [Total Design Time],
	Count(dbo.tblOrders.OrderNo) AS [Number of Orders] 
INTO #MyTempTable
FROM dbo.tblReps INNER JOIN dbo.tblOrders ON dbo.tblReps.Code = dbo.tblOrders.Rep_Code
GROUP BY dbo.tblReps.Name, dbo.tblReps.Active

SELECT @MyGrandTotal = SUM([Total Order Value])

SELECT 
[Rep Name],
[Active Rep],
[Total Order Value],
[Total AST],
[Total Design Time],
[Number of Orders],
[Total Order Value] / NullIf([Number of Orders], 0) as [Page Rate],
([Total Order Value] * 100.0) / @MyGrandTotal as [Overall Percentage]
FROM #MyTempTable
ORDER BY [Rep Name], [Active Rep]

Note that you should use CREATE TABLE #MyTempTable ... (rather than SELECT ... INTO #MyTempTable) for efficiency - but I am too lazy for this example!

Kristen

Meltdown
Starting Member

37 Posts

Posted - 2006-01-29 : 11:07:11

Hi Kristen, thanks a lot for your help, a couple of issues...

Your line SELECT @MyGrandTotal = SUM([Total Order Value])..is returning a Not a Valid Column error, I dont know why that is?

Also, is it possible to return the [Overall Percentage] column formatted as a percentage, to 2 decimal places?

Thanks



ALTER PROCEDURE dbo.OrdersGrouped2
AS
BEGIN
	SET NOCOUNT ON;
DECLARE @MyGrandTotal money

Create Table #TempTable
(
[Rep Name] nvarchar(30),
[Active Rep] bit,
[Total Order Value] money,
[Total AST] smallint,
[Total Design Time] smallint,
[Number of Orders] smallint
)

INSERT #TempTable
SELECT 
	dbo.tblReps.Name AS [Rep Name], 
	dbo.tblReps.Active AS [Active Rep],
	Sum(dbo.tblOrders.Prod_Price) AS [Total Order Value],
	Sum(dbo.tblOrders.AST_Value) AS [Total AST],
	Sum(dbo.tblOrders.Design_Time) AS [Total Design Time],
	Count(dbo.tblOrders.OrderNo) AS [Number of Orders] 
FROM dbo.tblReps INNER JOIN dbo.tblOrders ON dbo.tblReps.Code = dbo.tblOrders.Rep_Code
GROUP BY dbo.tblReps.Name, dbo.tblReps.Active


SELECT @MyGrandTotal = SUM([Total Order Value])

SELECT 
[Rep Name],
[Active Rep],
[Total Order Value],
[Total AST],
[Total Design Time],
[Number of Orders],
[Total Order Value] / NullIf([Number of Orders], 0) as [Page Rate],
([Total Order Value] * 100.0) / @MyGrandTotal as [Overall Percentage]
FROM #TempTable
ORDER BY [Rep Name], [Active Rep]

END

Kristen
Test

22859 Posts

Posted - 2006-01-29 : 11:11:30

Sorry should have been

SELECT @MyGrandTotal = SUM([Total Order Value])
FROM #TempTable

"Also, is it possible to return the [Overall Percentage] column formatted as a percentage, to 2 decimal places?"

You would be better off formatting it in your application layer. Alternatively CAST it to an appropriate datatype - 2d.p. decimal, or the CONVERT function has a couple of formatting options - see Books on Line for details

Kristen

Meltdown
Starting Member

37 Posts

Posted - 2006-01-30 : 05:59:14

Thanks a lot Kristen. got it sorted now

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources