calculate percentage

(1) By anonymous on 2022-08-16 16:42:10 [source]

Hi,

I have made a view that let mee see how many booktitles I have purchased for a writer. By example: Writer is Asimov 12 books, Vance 13 books and so on.

I have 2100 titles.

What I want is a view with percentage over my whole Bookdatabase per writer. So what is the percentage of Asimov in my bookcollection.

This is my view of counting the titles per writer:

CREATE VIEW NumberOf_Title_Writer as select Writer, Count(Writer) as NumberOfTitles from Books group by Writer

ORDER BY NumberOfTitles DESC;

Writer and Title are TEXT fields.

This works great, but now I want something like that with percentage.

Thank you for any attention

(2) By Keith Medcalf (kmedcalf) on 2022-08-16 17:02:41 in reply to 1 [link] [source]

Like so:

create view PercentageOf_Title_Writer as
 select Writer, 
         100.0 * count(*) / (select count(*) from books) as Percentage
    from books
group by writer;

(3.1) By Aask (AAsk1902) on 2022-08-16 17:18:04 edited from 3.0 in reply to 2 [link] [source]

(select count(*) from books)

Is this an SQLite speciality or is it missing a group by clause i.e. should it be

(select count(*) from books group by writer)

(4) By Keith Medcalf (kmedcalf) on 2022-08-16 17:24:59 in reply to 3.1 [link] [source]

No. In order to calculate the percentage of a pile of stuff that is purple you need two counts: The count of the stuffs that are purple, and the count of all the stuffs. The percentage of the stuffs that are purple is then given by:

count of purple stuffs / count of all stuffs

(select count(*) from books) gives a count of all the books -- it is called a "scalar expression", it is constant, and is only calculated once.

(5) By Keith Medcalf (kmedcalf) on 2022-08-16 17:30:32 in reply to 3.1 [link] [source]

(select count(*) from books group by writer)

This will return the count of the books by writer, not the count of all the books. Furthermore, since the paranthesis makes this a "scalar subquery" only one result will be returned. So the result will be the count of some arbitrary writer (you do not know which). This is not what you want.

(6) By David Jones (vman59) on 2022-08-16 17:34:29 in reply to 4 [link] [source]

Don't forget to multiply by 100 (or 100.0 to ensure floating point arithmetic) to get a percentage.

(7) By Aask (AAsk1902) on 2022-08-16 17:36:31 in reply to 5 [link] [source]

Got it.

(8) By Keith Medcalf (kmedcalf) on 2022-08-16 19:16:00 in reply to 7 [link] [source]

Note the alternate ways of spelling the same thing, all of which achieve the same result in practically the same manner:

  select writer, 
         100.0 * count(*) / (select count(*) from books) as percentage
    from books 
group by writer;

  select writer, 
         100.0 * count(*) / countbooks as percentage
    from (
          select count(*) as countbooks 
            from books
         ), books 
group by writer;

    with constants(countbooks) as 
         (
             select count(*) 
               from books
         )
  select writer, 
         100.0 * count(*) / countbooks as percentage
    from constants, books
group by writer;

(9) By jose isaias cabrera (jicman) on 2022-08-16 19:35:27 in reply to 8 [link] [source]

Create table books values (book,writer);
begin;
Insert into books values  ('book0','X');
Insert into books values  ('book1','X');
Insert into books values  ('book2','X');
Insert into books values  ('book3','Z');
Insert into books values  ('book4','Y');
Insert into books values  ('book5','Z');
Insert into books values  ('book6','Y');
Insert into books values  ('book7','Y');
end;

sqlite>   select writer,
   ...>          100.0 * count(*) / (select count(*) from books) as percentage
   ...>     from books
   ...> group by writer;
┌────────┬────────────┐
│ writer │ percentage │
├────────┼────────────┤
│ X      │ 37.5       │
│ Y      │ 37.5       │
│ Z      │ 25.0       │
└────────┴────────────┘
Run Time: real 0.000 user 0.000000 sys 0.000000
sqlite>
sqlite>   select writer,
   ...>          100.0 * count(*) / countbooks as percentage
   ...>     from (
   ...>           select count(*) as countbooks
   ...>             from books
   ...>          ), books
   ...> group by writer;
┌────────┬────────────┐
│ writer │ percentage │
├────────┼────────────┤
│ X      │ 37.5       │
│ Y      │ 37.5       │
│ Z      │ 25.0       │
└────────┴────────────┘
Run Time: real 0.015 user 0.015625 sys 0.000000
sqlite>
sqlite>     with constants(countbooks) as
   ...>          (
   ...>              select count(*)
   ...>                from books
   ...>          )
   ...>   select writer,
   ...>          100.0 * count(*) / countbooks as percentage
   ...>     from constants, books
   ...> group by writer;
┌────────┬────────────┐
│ writer │ percentage │
├────────┼────────────┤
│ X      │ 37.5       │
│ Y      │ 37.5       │
│ Z      │ 25.0       │
└────────┴────────────┘
Run Time: real 0.000 user 0.000000 sys 0.000000

Any questions? :-)

(10) By anonymous on 2022-08-16 20:06:25 in reply to 9 [link] [source]

Wow,

Thank you for your reactions. I appreciate that very much. Tomorrow I wil study the answers and let you know the result.

Thanks again.

(11) By anonymous on 2022-08-17 13:58:14 in reply to 2 [link] [source]

This works great, I am very glad.

create view PercentageOf_Title_Writer as select Writer, round(100.0 * count() / (select count() from Books), 2) as Percentage from Books group by Writer;

I added the round function, so it's look nicer.

Thank you kmedcalf for your solution, and the others, thanks for thinking along.