Calculate a Percentage based off 2 conditions for 2 columns (1 cond per 1 col) in sqlite-CodePudding

Given the table format below

id  book_name(TEXT) rating(REAL)
1   The Book          50.0
2   Perks Of          30.2
3   book game         100.0 
4   Hobbit            80.0

I want to calculate a percentage of book_name that contain "book" in the text (not case sensitive) and has a rating above 50.0. The percentage would be calculated as (# of rows that meet 2 conditions / # of total rows). The answer in this case is calculated by 2/4 = 50.0 (this is in percentage form)

I'd want to return 50.0 as an answer

CodePudding user response：

You can just use a CASE statement inside SUM() to do your conditional count -

SELECT ROUND(SUM(CASE WHEN book_name LIKE '%book%' AND rating >= 50 THEN 1 END)/COUNT(*) * 100, 1)
FROM books

CodePudding user response：

You can use lower then filter using regex on conditions

import pandas as pd
data = {"id":[1,2,3,4], "book_name(TEXT)":["The Book","Perks Of","book game","Hobbit"],"rating(REAL)":[50.0,30.2,100.0, 80.0] }
data = pd.DataFrame(data)
data["lower"] = data["book_name(TEXT)"].str.lower()
data1 = data[data.lower.str.contains("book")==True]
data1 = data1[data1["rating(REAL)"]>=50.0]
(len(data1)/len(data))*100