Make output value a percentage

[ScotWilder]

Hi folks,

Having a major head scratch moment with percentages in t-sql output, I need the end result of a count 1/count 2 output to be a percentage value of 00.00% (e.g. two decimal places.

The result of this example should be count 1 (14) / count 2 (146) = 9.59%

I've seen a separate thread to do with updates using percentages but it's not clicking I'm afraid and attempts to put formatting in have failed hence it doesn't have anything around to calculate percentage, any help gratefully received!

:banghead:

SELECT (SELECT COUNT(PROSPECTACTION.PROSPECTACTIONID) AS 'Ask/Close'
FROM PROSPECTACTION LEFT OUTER JOIN
PROSPECT ON PROSPECTACTION.SERIALNUMBER = PROSPECT.SERIALNUMBER
WHERE (PROSPECT.PRIMARYFUNDRAISER = 'jillians') AND (PROSPECTACTION.STAGE IN ('5 - Ask','6 - Close')))/(SELECT COUNT(PROSPECTACTION.PROSPECTACTIONID) AS 'Total Prospects'
FROM PROSPECTACTION LEFT OUTER JOIN
PROSPECT ON PROSPECTACTION.SERIALNUMBER = PROSPECT.SERIALNUMBER
WHERE (PROSPECT.PRIMARYFUNDRAISER = 'jillians')) AS 'CalculatedPercentage'/QUOTE]

 

[mdlueck]

Here is SQL which will either return "N/A" if no records were in the table, or "15% of 13" if there was data in the table:

BEGIN TRAN

-- Define some variables to receive the results of queries so that they
-- are available to subsequent queries
DECLARE @count AS integer;
DECLARE @donecount AS integer;

-- Define a query to find out how many records are exist for this product
SELECT @count = COUNT(*)
FROM [dbo].[aoe]
INNER JOIN [dbo].[aoeaoestationlink] AS [aoesl] ON [aoe].[id] = [aoesl].[aoeid]
INNER JOIN [dbo].[aoestation] AS [aoes] ON [aoesl].[aoesid] = [aoes].[id]
INNER JOIN [dbo].[aoefixture] AS [aoef] ON [aoes].[id] = [aoef].[aoesid]
WHERE [aoe].[id] = @aoeid

-- Check if any matching records were found
IF @count = 0
  SELECT 'N/A' AS [result]
ELSE
  -- Go ahead and compute the results since records existed
  BEGIN
    -- Define a query to find out how many records are in "done" status
    SELECT @donecount = COUNT(*)
    FROM [dbo].[aoe]
    INNER JOIN [dbo].[aoeaoestationlink] AS [aoesl] ON [aoe].[id] = [aoesl].[aoeid]
    INNER JOIN [dbo].[aoestation] AS [aoes] ON [aoesl].[aoesid] = [aoes].[id]
    INNER JOIN [dbo].[aoefixture] AS [aoef] ON [aoes].[id] = [aoef].[aoesid]
    WHERE [aoe].[id] = @aoeid
    AND [aoef].[cmplstatusid] = 1

    -- Define a query to prepare the output value based on the variables which have been prepared
    SELECT CAST(ROUND(CAST(@donecount AS float) / CAST(@count AS float) * 100, 0) AS varchar(10)) + '%' + ' of ' + CAST(@count AS varchar(10)) AS [result]
  END

COMMIT TRAN

 

[ScotWilder]

Absolutely perfect, many thanks!!

All I did in addition was added the 'donecount' to the front of the statement so it read as '14 = 10% of 146' (example)

 

[mdlueck]

You are most welcome, ScotWilder. Thank you for coming back to share your success.