SQL Query to count entries in the column and get percentage

Question

student	have new books
alice	yes
bob	yes
candace	no
dalia	no
edd	no
frank	yes

expected output 50

essentially i want to count the total number of rows and and students that have new books and get the percentage of students with new books

Answer 1

Use the conditional aggregation as follows:

select count(case when have_new_book = 'yes' then 1 end) / count(*)
 from your_Table

Answer 2

Three methods shown:

create table t(student,have_new_books);
insert into t values
  ('alice',     'yes'),
  ('bob',       'yes'),
  ('candace',   'no'),
  ('dalia',     'no'),
  ('edd',       'no'),
  ('frank',     'yes');

select sum(case have_new_books when 'yes' then 1 else 0 end)*100 / count(*) yes_percent
  from t;

select have_new_books,count(*)*100/(select count(*) from t) percent
  from t
  group by have_new_books;

select sum(have_new_books='yes')*100/count(*) yes_percent
  from t;

Tested with SQLite3 but they should work on most other engines (3rd possibly only on SQLite3).

Output:

┌─────────────┐
│ yes_percent │
├─────────────┤
│ 50          │
└─────────────┘
┌────────────────┬─────────┐
│ have_new_books │ percent │
├────────────────┼─────────┤
│ no             │ 50      │
│ yes            │ 50      │
└────────────────┴─────────┘
┌─────────────┐
│ yes_percent │
├─────────────┤
│ 50          │
└─────────────┘