Solved

How do I get the percentage

Posted on 2016-09-26
24
37 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 25

Expert Comment

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

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

Expert Comment

by:Pawan Kumar
ID: 41815859
Try this ..

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

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 25

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 25

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 25

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 25

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 25

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Backup & Restore 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.
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

863 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

25 Experts available now in Live!

Get 1:1 Help Now