Solved

How do I get the percentage

Posted on 2016-09-26
24
50 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
  • 4
  • +1
24 Comments
 
LVL 29

Expert Comment

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

Expert Comment

by:Pawan Kumar
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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41815859
Try this ..

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

Expert Comment

by:Pawan Kumar
ID: 41815871
Sorry updated..

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

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 17

Expert Comment

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

Expert Comment

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

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

Expert Comment

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

Order of Operations - BODMAS

Use above code that should work.
0
 
LVL 49

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 29

Expert Comment

by:Pawan Kumar
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
 
LVL 17

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 29

Expert Comment

by:Pawan Kumar
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 49

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 49

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 29

Accepted Solution

by:
Pawan Kumar 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 49

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

630 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