Percentage Calculation
-
Hello everyone,
I have a question in regards to how to calculate a percentage of counts between 2 tables in SQL Server.
Any information would be greatly apprecaited.
Thank you,
Peter
Peter M. Florenzano
Database Administrator
-
I don't know if this is exactly what you need but here is s guess:
select cast((select cast(count*) as float) from table1 where YourCondition1) /
(select cast(count(*) as float) from table2 where YourCondition2) * 100 as numeric(8,4) as Pct
That one gives a percentage of the number of rows between 2 tables
I hope this helps
Bye
Gabor
Bye
Gabor -
Hi Gabor,
This worked excellent, thank you. Now I need to truncate the remainder of the numbers to the right of the decimal. Is there a specific function in SQL Server for that?
Thanks,
Pete
Edited by - PFlorenzano on 11/06/2003 10:12:02 AM
Peter M. Florenzano
Database Administrator
-
Select Cast (0.01*(Select Count(*) from table1 where YourCondition1)/(Select Count(*) from table2 where YourCondition2) as int )
* Noel -
Hello, Peter.
I had a similar situation just recently.
What worked for me was to make each count an int or bigint, and set the variable for Percent to be decimal(5,2).
Table Variables were used so all rows only needed to be queried once and the result set was reused for the counts.
The function code looks like this (generally):
GO
Create Function dbo.udf_PctAppsInBusinessDays
( @StartDate DATETIME
, @EndDate DATETIME
, @DaysMax INT = 8
)
RETURNS
@retAppCounts TABLE
( AppsInBusinessDays int
, AppsTotal int
, AppsPercent decimal(5,2)
)
AS
BEGIN
DECLARE @AppsInBusinessDays int
DECLARE @AppsTotal int
DECLARE @AppsPercent decimal(5,2)
DECLARE @TableVar table
( fieldname1 datatype
, fieldname2 datatype
, etc....
)
INSERT INTO @TableVar
SELECT columns
FROM table(s)
Where condition(s)
.
.
.
SET @AppsInBusinessDays =
(Select COUNT(*)
From @TableVar
WHERE (BusinessDays <= @DaysMax )
SET @AppsTotal =
(Select COUNT(*)
From @TableVar)
SET @AppsPercent =
((@AppsInBusinessDays*1.0) / @AppsTotal)*100
INSERT @retAppCounts
SELECT
AppsInBusinessDays = NZ(@AppsInBusinessDays,0)
, AppsTotal = NZ(@AppsTotal,0)
, AppsPercent = NZ(@AppsPercent,0)
RETURN
END
The returned values are:
AppsInBusinessDays AppsTotal AppsPercent
4,520.00 _________ 6,326.00 _ 71.45
** NOTE: Watch out for Divide by Zero errors. Right now I can't get to the server drive, or I would have sent the whole code.
Success to you!
NJJ
Norm Johnson
Norm Johnson
"Keep smiling ... it gives your face something happy to do
... and it makes people wonder what you're up to!"
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply