Generate 'calculated values' for rows within Stored Procedure

I'm having a problem with generating a 'calculated' percentage value as
noted in the sample output below:
#######
Luxury 822 .34602709630
Standard 433 .34602709630
Custom 397 .34602709630
Modified 285 .34602709630
Other 222 .34602709630
More... 676 .34602709630
#######
The 'VP_VClass' column (left) correctly shows each classification, and
provides the correct count for each. However the percentage for each
classification is shown 'incorrectly' as it should be a percentage for each
instead of for the 'sum' of the classifications count, like so:
#######
Luxury 822 .10032954961
Standard 433 .05284999389
#######
The SP is as follows, where the function [dbo.udf_Current_InventoryFunction
()] provides the 'total' count (which in the examples above is 8,193).
#######
ALTER PROCEDURE dbo.usp_VClass_Breakdown
AS
SELECT TOP 100 PERCENT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass,
(SELECT CAST(COUNT(VP_VClass) AS NUMERIC)
FROM dbo.vw_VClass_BreakdownView
WHERE (Active = 1) AND (Current = 1)) /
(SELECT *
FROM dbo.udf_Current_InventoryFunction
()) AS Expr1
FROM dbo.vw_VClass_BreakdownView
WHERE (Active = 1) AND (Current = 1)
GROUP BY VP_VClass
ORDER BY COUNT(VP_VClass) DESC
#######
Any thoughts or suggestions would be appreciated. Thanks.
--
Message posted via http://www.sqlmonster.comHi,
The Query can be re-written as:
=============================ALTER PROCEDURE dbo.usp_VClass_Breakdown
AS
SELECT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass, COUNT(VP_VClass) /
udf_Current_InventoryFunction() PercentOfVP
FROM dbo.vw_VClass_BreakdownView
WHERE (Active = 1) AND (Current = 1)
GROUP BY VP_VClass
ORDER BY COUNT(VP_VClass) DESC
================
I believe this addressed your question. If there are any more problem please
revert back
thanks and regards
Chandra
"The Gekkster via SQLMonster.com" wrote:
> I'm having a problem with generating a 'calculated' percentage value as
> noted in the sample output below:
> #######
> Luxury 822 .34602709630
> Standard 433 .34602709630
> Custom 397 .34602709630
> Modified 285 .34602709630
> Other 222 .34602709630
> More... 676 .34602709630
> #######
> The 'VP_VClass' column (left) correctly shows each classification, and
> provides the correct count for each. However the percentage for each
> classification is shown 'incorrectly' as it should be a percentage for each
> instead of for the 'sum' of the classifications count, like so:
> #######
> Luxury 822 .10032954961
> Standard 433 .05284999389
> #######
> The SP is as follows, where the function [dbo.udf_Current_InventoryFunction
> ()] provides the 'total' count (which in the examples above is 8,193).
> #######
> ALTER PROCEDURE dbo.usp_VClass_Breakdown
> AS
> SELECT TOP 100 PERCENT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass,
> (SELECT CAST(COUNT(VP_VClass) AS NUMERIC)
> FROM dbo.vw_VClass_BreakdownView
> WHERE (Active = 1) AND (Current = 1)) /
> (SELECT *
> FROM dbo.udf_Current_InventoryFunction
> ()) AS Expr1
> FROM dbo.vw_VClass_BreakdownView
> WHERE (Active = 1) AND (Current = 1)
> GROUP BY VP_VClass
> ORDER BY COUNT(VP_VClass) DESC
> #######
> Any thoughts or suggestions would be appreciated. Thanks.
> --
> Message posted via http://www.sqlmonster.com
>|||Hi Chandra,
Thanks for the assist - that took care of it.
--
Message posted via http://www.sqlmonster.com|||Good to know that the solution addressed your needs. Really appreciate if you
can rate the Post.
This can be done by answering "Was this post helpful to you?"
"The Gekkster via SQLMonster.com" wrote:
> Hi Chandra,
> Thanks for the assist - that took care of it.
> --
> Message posted via http://www.sqlmonster.com
>