Solved

How do I get the percentage

Posted on 2016-09-26
24
41 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 28

Expert Comment

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

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41815859
Try this ..

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

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 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 28

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 28

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 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 28

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 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 28

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 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 28

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 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
kill process lock Sql server 9 46
Showing random records from database 10 36
SQL Server 2012 r2 - Sum totals 2 23
SQL Improvement  ( Speed) 14 26
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

776 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