Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 56
  • Last Modified:

How do I get the percentage

Hi,

I need to get the percentage of pass and fail

My output
  Outcome    TotalCount
      Fail               1  
     Pass               10  
 
My Query
SELECT count(Outcome) as TotalCount FROM ReptResult GROUP BY Outcome  

Thank you for your help.
Lulu
0
lulu50
Asked:
lulu50
  • 9
  • 8
  • 4
  • +1
2 Solutions
 
Pawan KumarDatabase ExpertCommented:
Please post the table structure
0
 
Pawan KumarDatabase ExpertCommented:
or may be try this ..

SELECT Outcome , count(Outcome) as TotalCount FROM ReptResult GROUP BY Outcome
0
 
lulu50Author Commented:
This is the main query called ReptResult from it I create my second query to do the count but instead of the count I want the percentage.
   

 <cfquery name="ReptResult" datasource="#strDSN#" username="#strUID#" password="#strPWD#">
select TC.ContentID, TC.SubContentID, TC.TicketID, TC.CCACaseID, TC.Name, TC.Headline, TC.Category2ID, TC.Outcome, TC.ModifiedByID, TC.ModifiedBy, TC.CreatedDate, L2P.Name as NameType 
from TestingContent TC
left join TestingCategory2_LookUp L2P on TC.Category2ID = L2P.Category2ID
where (ContentID is not null)
</cfquery>




<cfquery dbtype="query" name="ChartRpt">	
    SELECT Outcome, count(Outcome) as TotalCount 
      FROM ReptResult
      GROUP BY Outcome
</cfquery>

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Pawan KumarDatabase ExpertCommented:
Try this ..

SELECT Outcome , count(Outcome) * 100. / 100 as PercentageCount FROM ReptResult GROUP BY Outcome
0
 
Pawan KumarDatabase ExpertCommented:
Sorry updated..

SELECT Outcome , count(Outcome) * 100. / count(Outcome) as PercentageCount FROM ReptResult GROUP BY Outcome
0
 
John TsioumprisSoftware & Systems EngineerCommented:
Well it is not pretty but it should do the job
SELECT Final.Outcome
    ,Final.TotalCount
    ,Final.Totals
    ,Round([TotalCount] / [Totals],4)*100 AS PercentOfTotalCounts
FROM (
    SELECT DISTINCT Table1.Outcome, Table1.TotalCount, Sum(Table1_1.TotalCount) AS Totals
FROM Table1, Table1 AS Table1_1
GROUP BY Table1.Outcome, Table1.TotalCount    ) Final

Open in new window

0
 
lulu50Author Commented:
I made some changes to my Query

	<cfquery dbtype="query" name="ChartRpt2">	
   		SELECT Outcome , (count(Outcome) * 100 / #TotalCountNum# ) as PercentageCount FROM ReptResult GROUP BY Outcome 
	</cfquery>

Open in new window


not I'm getting the output this:
Outcome PercentageCount
Fail  9.09090909091  
Pass  90.9090909091  

How can I just do Fail 9%   Pass 90%  remove all numbers after the decimal ?
0
 
John TsioumprisSoftware & Systems EngineerCommented:
with the round command....look at my code...
0
 
Pawan KumarDatabase ExpertCommented:
Brackets ...use below..

SELECT Outcome , (count(Outcome) * 100.) / count(Outcome) as PercentageCount FROM ReptResult GROUP BY Outcome
0
 
Pawan KumarDatabase ExpertCommented:
You are getting 9 because of ...

Order of Operations - BODMAS

Use above code that should work.
0
 
PortletPaulCommented:
SELECT DISTINCT
      Outcome
    , (Outcome_count * 100.0) / (Total_count * 1.0) AS Percentage
FROM (
      SELECT
            outcome
          , COUNT(*) OVER (PARTITION BY Outcome)      Outcome_count
          , COUNT(*) OVER (PARTITION BY ( SELECT 1 )) Total_count
      FROM ReptResult 
      ) d

Open in new window

{+edit} forgot  from clause.. sorry

declare @ReptResult table
    ([Outcome] varchar(1))
;
    
INSERT INTO @ReptResult 
    ([Outcome])
VALUES
    ('p'),
    ('p'),
    ('p'),
    ('p'),
    ('p'),
    ('p'),
    ('p'),
    ('p'),
    ('p'),
    ('p'),
    ('f')
;

SELECT DISTINCT
      Outcome
    , (Outcome_count * 100.0) / (Total_count * 1.0) AS Percentage
FROM (
      SELECT
            outcome
          , COUNT(*) OVER (PARTITION BY Outcome)      Outcome_count
          , COUNT(*) OVER (PARTITION BY ( SELECT 1 )) Total_count
      FROM @ReptResult 
      ) d


Outcome Percentage       
------- ---------------- 
f       9.09090909090909 
p       90.9090909090909 

(2 row(s) returned)

(11 row(s) affected)

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
Or may be even small one ..

SELECT
       DISTINCT outcome
          , ( COUNT(*) OVER (PARTITION BY Outcome) * 100.0 ) / COUNT(*) OVER () PercentageCount 
FROM ReptResult           
     

Open in new window

0
 
John TsioumprisSoftware & Systems EngineerCommented:
My bad i thought it was Ms Access......please ignore my posts
0
 
lulu50Author Commented:
Pawan,

I'm getting an error

Query Of Queries syntax error.
Encountered "COUNT ( * ) OVER. Incorrect Select List,
0
 
Pawan KumarDatabase ExpertCommented:
Please try  below.......

--

SELECT
       DISTINCT outcome
          ,  (  ( COUNT(*) OVER (PARTITION BY Outcome) * 100.0 )    /    ( COUNT(*) OVER () )  ) 
AS
PercentageCount 
FROM ReptResult    


--

Open in new window

0
 
lulu50Author Commented:
Error Executing Database Query.  

Query Of Queries syntax error.
Encountered "COUNT ( * ) OVER. Incorrect Select List,  
 
SELECT DISTINCT outcome , ( ( COUNT(*) OVER (PARTITION BY Outcome) * 100.0 ) / ( COUNT(*) OVER () ) ) AS PercentageCount FROM ReptResult
0
 
PortletPaulCommented:
would you please try mine, I have shown that it works.
0
 
lulu50Author Commented:
SELECT Outcome , (count(Outcome) * 100 / #TotalCountNum#) as PercentageCount FROM ReptResult GROUP BY Outcome

the output is:
  Outcome PercentageCount
       Fail               9.09090909091  
      Pass              90.9090909091  

but what I want is this
  Outcome PercentageCount
       Fail               9
      Pass              90
0
 
PortletPaulCommented:
is this cold fusion? it might not understand the over() clause
0
 
lulu50Author Commented:
Portlet

The following information is meant for the website developer for debugging purposes.  

Error Occurred While Processing Request  
Error Executing Database Query.  

Query Of Queries syntax error.
Encountered "(.  
 
SELECT DISTINCT Outcome , (Outcome_count * 100.0) / (Total_count * 1.0) AS Percentage FROM ( SELECT outcome , COUNT(*) OVER (PARTITION BY Outcome) Outcome_count , COUNT(*) OVER (PARTITION BY ( SELECT 1 )) Total_count FROM ReptResult ) d
0
 
lulu50Author Commented:
yes coldfusion but uses SQL database
0
 
Pawan KumarDatabase ExpertCommented:
Use below...

--

SELECT Outcome, ( ( counts * 1.0 ) / tot ) Percentages FROM 
(
	SELECT 
		DISTINCT
		  Outcome
		FROM @ReptResult x
)r
CROSS APPLY
(
	SELECT COUNT(*) tot FROM @ReptResult
)s		
CROSS APPLY
(
	SELECT COUNT(*) counts FROM @ReptResult p
	WHERE r.Outcome = p.Outcome
)t	  

--

Open in new window

0
 
PortletPaulCommented:
There are a variety of ways to control the final output.
Outcome Percentage 
------- ---------- 
f       9          
p       91         

(2 row(s) returned)

Open in new window

declare @ReptResult table
    ([Outcome] varchar(1))
;
    
INSERT INTO @ReptResult 
    ([Outcome])
VALUES
    ('p'),
    ('p'),
    ('p'),
    ('p'),
    ('p'),
    ('p'),
    ('p'),
    ('p'),
    ('p'),
    ('p'),
    ('f')
;

SELECT DISTINCT
      Outcome
      , cast(ROUND((Outcome_count * 100.0) / (Total_count * 1.0),0) as int) AS Percentage
FROM (
      SELECT
            outcome
          , COUNT(*) OVER (PARTITION BY Outcome)      Outcome_count
          , COUNT(*) OVER (PARTITION BY ( SELECT 1 )) Total_count
      FROM @ReptResult 
      ) d
      

Open in new window

0
 
lulu50Author Commented:
Thank you
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 9
  • 8
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now