I would not be able to do what I want :(, I explain:

I have two tables:

Table1

Table2

I am looking for a query where I can get FALSE/(FALSE TRUE).

So far I managed to do it over the total using this:

select 
(select count(*) from (select Drink from Table1
where Drink=False)) as F,
(select count(*) from (select Drink from Table1
where Drink=True)) as T,
(F/(F T)) as total

With that I can get the total of the column I want, I just have to change, if I want, Drink-->Food.

But now I would like the final result by Country, that is, something similar to this:

Expected result

In order not to break the forum rules, I'll leave what I've tried so far:

select * from (select distinct t2.Country_Name, count(t1.Drink)
from Table 1 t1
left join Table 2 t2 on t2.Country_ID=t1.Country_ID
where Drink=False
group by t2.Country_Name) as F
union
select * from (select distinct t2.Country_Name, count(t1.Drink)
from Table 1 t1
left join Table 2 t2 on t2.Country_ID=t1.Country_ID
where Drink=True
group by t2.Country_Name) as T

But it doesn't even bring me what I expect :(

I don't know if it helps much, but I'm using Snowflake

Any help is welcome. Thank you!

CodePudding user response：

The first query could be simpliefied using COUNT_IF:

SELECT COUNT_IF(Drink=False)/COUNT(*)
FROM Table1;

Grouped by Country:

SELECT t2.Country_Name, COUNT_IF(t1.Drink=False)/COUNT(*)
FROM Table1 AS t1
JOIN Table2 AS t2
  ON t2.Country_ID=t1.Country_ID
GROUP BY t2.Country_Name;