CODEWITHSUNDEEP
sqloracle-database
derpyderp
derpyderp

Reputation: 343

Dividing by a subquery by a count(*)

I'm running a query and trying to get a percentage returned for failures vs successes in the database.

SELECT ((SELECT count(*) FROM validation WHERE request_date BETWEEN (SYSDATE - 30/(24*60)) AND SYSDATE AND response != 'VERIFIED') / count(*))
AS percentage
FROM (SELECT * FROM validation
WHERE request_date BETWEEN (SYSDATE - 30/(24*60)) AND SYSDATE)
;

It's returning the error 'ORA-00937: not a single-group group function'. I would have thought that the subquery would be returning a single value so I would simply be able to divide it by the count(*). Aby ideas?

Upvotes: 2

Views: 2070

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

Personally, I think the best way to write the query is:

select avg(case when response <> 'VERIFIED' then 1.0 else 0 end) as percentage
from validation v
where request_date BETWEEN (SYSDATE - 30/(24*60)) AND SYSDATE;

You can also write the where clause as:

where request_date between sysdate - interval '30' minute and sysdate

Upvotes: 1

sstan
sstan

Reputation: 36473

You're over complicating the query. You don't need any subqueries:

select count(case when response != 'VERIFIED' then 'X' end) / count(*) as percentage
  from validation
 where request_date BETWEEN (SYSDATE - 30/(24*60)) AND SYSDATE

Upvotes: 3

Anton M.
Anton M.

Reputation: 472

May be you need just 2 subquery from dual? 

 Select 
   (SELECT count(*) FROM validation WHERE request_date BETWEEN (SYSDATE - 30/(24*60)) AND SYSDATE AND response != 'VERIFIED')/
   (SELECT count(*) FROM validation WHERE request_date BETWEEN (SYSDATE - 30/(24*60)) AND SYSDATE)
 from dual;

Upvotes: 1

Related Questions