This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Please check the below input & output data, find a book name which is printed on more than 50% of languages.
Input data:
|
1 2 3 4 5 6 7 |
bookname lang ---------- ---------- ABC EN XYZ FRE ABC EN ANV EN ABC ITA |
Require output:
|
1 2 3 |
bookname ---------- ABC |
Create a table with sample data:
|
1 2 3 4 5 6 7 8 9 |
create table books ( bookname varchar(10) ,lang varchar(10) ) insert into books values ('ABC','EN'),('XYZ','FRE'),('ABC','EN') ,('ANV','EN'),('ABC','ITA') |
Solution 1 (Using CTE):
|
1 2 3 4 5 6 7 8 9 10 |
with ctetest as ( select bookname,COUNT(1) as bookcount ,(select COUNT(distinct lang) from books) as langcount from books group by bookname ) select bookname from ctetest where (bookcount / langcount) > 0.5 |
Solution 2 (Using Subquery):
|
1 2 3 4 5 6 7 8 9 10 |
select bookname from ( select bookname, count(1) bookcount ,(select count(distinct lang) from books) langcount from books group by bookname )as t where (bookcount/langcount)>0.5 |
