sql - SQLite complex query -

for page on site need list of distinct usernames ordered descending percentage of deleted tweets. , column deleted1 1 means deleted tweet, 0 means not deleted (tweet exists), , 2 unknown. know complex query group by... use help.

here table:

create table twitt_tb (    link     text primary key not null,    username text             not null,    content  text             not null,    datum    integer          not null,    avatar   text             not null,    nik      text             not null,    deleted1 integer  check (deleted1 in (0, 1, 2)) not null ); 

edit: additional clarification: need list of usernames ordered percentage, , percentage each user. percentage calculated users-deleted-tweets/total-tweets-of-that-user, , not user-deleted-tweets/total-tweets-of-all-users.

the following gives proportion of deleted tweets user -- of tweets user known.

select username,        (sum(case when delete1 = 1 1.0 else 0.0 end) /         sum(case when delete1 in (0, 1) 1.0 else 0.0 end)        ) pdeleted twitt_tb t group username order pdeleted desc; 

if want include tweets user, can formulate as:

       (sum(case when delete1 = 1 1.0 else 0.0 end) /         count(*)        ) pdeleted 

or more as:

avg(case when delete1 = 1 1.0 else 0.0 end) pdeleted 

this includes proportion in output. if wanted user name without proportion, put formula in order by clause instead of select clause.

and finally, if want represented percent percentage sign, need convert number string , append '%'.