CODEWITHSUNDEEP
sqloracle-database
fite
fite

Reputation: 13

Using Subqueries to get a percentage from a number of SQL columns

I have an oracle database ischema with a 'Goals' table, the table has 5 columns: 

G_ID - Int  
M_ID - Int  
G_Method - Text  
G_X_Loc - Int   
G_Y_Lo - Int  
G_Time - Int

The G_X_Loc and G_Y_Loc are integers between -5 and +5. I need to work out, as a percentage, how many goals were scored between Y0 to Y+5 and how many were scored between Y0 and Y-5.

So far I've managed to use the following query to tell me the numbers for 0 to +5, which is 56, but I'm struggling to work it as a percentage of the total rows (107).

Select COUNT(G_ID) AS Y_POS_GOALS  
FROM GOALS  
WHERE G_Y_LOC IN (Select G_Y_LOC  
FROM GOALS  
WHERE G_Y_LOC BETWEEN 0 AND 5)

Could anyone give me a steer on a better way to go about this?

Upvotes: 1

Views: 276

Answers (2)

MT0
MT0

Reputation: 167981

SELECT AVG( CASE WHEN g_y_log >= 0 THEN 100 ELSE 0 END ) AS g_y_positive_percentage,
       AVG( CASE WHEN g_y_log <= 0 THEN 100 ELSE 0 END ) AS g_y_negative_percentage
FROM   goals;

Upvotes: 3

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

I don't know Oracle Apex, but below query is standard SQL and runs in Oracle and most other RDBMS:

select 
  count(*) as total,
  count(case when g_y_loc between 0 and 5 then 1 end) as cnt_pos,
  count(case when g_y_loc between -5 and 0 then 1 end) as cnt_neg,
  count(case when g_y_loc between 0 and 5 then 1 end) / count(*) * 100 as prz_pos,
  count(case when g_y_loc between -5 and 0 then 1 end) / count(*) * 100 as prz_neg
from goals;

Upvotes: 1

Related Questions