CODEWITHSUNDEEP
sqloracle-databasepercentage
user14954375
user14954375

Reputation:

Percentage in SQL using query

This is my table and i want to find the percentage of those who have vaccination_date1 as not null. My guess is ,it would look like

SELECT COUNT(SSRN) FROM CITIZEN WHERE VACCINATION_DATE1 IS NOT NULL/COUNT(SSRN) FROM CITIZEN

but i am not sure,any ideas?

CREATE TABLE CITIZEN(
fname varchar(60) not null,
lname varchar(60) not null,
citizen_id varchar(10) not null,
ssrn number(20) primary key,
birthday date not null,
age number(3),
adress varchar(60) not null,
adr_num number(15) not null,
postal_code number(15)not null,
town varchar(60),
state varchar(60),
country varchar(60)not null,
vaccine varchar(15),
vaccination_date1 char(1),
vaccination_date2 char(1),
constraint citizen_country_fk foreign key (country) references country(country_name),
constraint citizen_vaccine_fk foreign key (vaccine) references vaccine(vaccine_id)
);

Upvotes: 0

Views: 671

Answers (3)

William Robertson
William Robertson

Reputation: 15991

You don't need any CASE expression, as count only counts non-null values. Therefore you can just use:

select count(*) as citizen_count
     , count(vaccination_date1) as vaccinated
     , round(100 * count(vaccination_date1)/count(*)) as percent_vaccinated
from   citizen;

CITIZEN_COUNT VACCINATED PERCENT_VACCINATED
------------- ---------- ------------------
          107         35                 33

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

I would use avg():

select avg(case when vaccination_date1 is not null then 1.0 else 0 end) 
from citizen;

If you want a value between 0 and 100:

select avg(case when vaccination_date1 is not null then 100.0 else 0 end) 
from citizen;

And if you want this formatted as a string with %:

select to_number(avg(case when vaccination_date1 is not null then 100.0 else 0 end), 'FM990.00') || '%'
from citizen;

Upvotes: 1

user5683823
user5683823

Reputation:

The simplest and most efficient way is to do the whole computation in a single query. Something like this:

select count(case when vaccination_date1 is not null then 1 end) / count(*)
from .....

This will return a number like 0.45, for example; left to you to display it as a percentage (45%), and to give the column a reasonable alias.

Upvotes: 0

Related Questions