Solved

How do I get the percentage

Posted on 2016-09-26
24
34 Views
Last Modified: 2016-09-26
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
Comment
Question by:lulu50
  • 9
  • 8
  • 4
  • +1
24 Comments
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41815815
Please post the table structure
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41815822
or may be try this ..

SELECT Outcome , count(Outcome) as TotalCount FROM ReptResult GROUP BY Outcome
0
 

Author Comment

by:lulu50
ID: 41815848
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
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41815859
Try this ..

SELECT Outcome , count(Outcome) * 100. / 100 as PercentageCount FROM ReptResult GROUP BY Outcome
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41815871
Sorry updated..

SELECT Outcome , count(Outcome) * 100. / count(Outcome) as PercentageCount FROM ReptResult GROUP BY Outcome
0
 
LVL 13

Expert Comment

by:John Tsioumpris
ID: 41815879
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
 

Author Comment

by:lulu50
ID: 41815880
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
 
LVL 13

Expert Comment

by:John Tsioumpris
ID: 41815881
with the round command....look at my code...
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41815882
Brackets ...use below..

SELECT Outcome , (count(Outcome) * 100.) / count(Outcome) as PercentageCount FROM ReptResult GROUP BY Outcome
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41815885
You are getting 9 because of ...

Order of Operations - BODMAS

Use above code that should work.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41815888
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
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41815895
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 13

Expert Comment

by:John Tsioumpris
ID: 41815901
My bad i thought it was Ms Access......please ignore my posts
0
 

Author Comment

by:lulu50
ID: 41815905
Pawan,

I'm getting an error

Query Of Queries syntax error.
Encountered "COUNT ( * ) OVER. Incorrect Select List,
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41815908
Please try  below.......

--

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


--

Open in new window

0
 

Author Comment

by:lulu50
ID: 41815916
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41815917
would you please try mine, I have shown that it works.
0
 

Author Comment

by:lulu50
ID: 41815918
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41815920
is this cold fusion? it might not understand the over() clause
0
 

Author Comment

by:lulu50
ID: 41815921
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
 

Author Comment

by:lulu50
ID: 41815924
yes coldfusion but uses SQL database
0
 
LVL 18

Accepted Solution

by:
Pawan Kumar Khowal earned 250 total points
ID: 41815927
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
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 250 total points
ID: 41815930
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
 

Author Closing Comment

by:lulu50
ID: 41815963
Thank you
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now