SQL - Selecting countries with highest count-CodePudding

Hello and thanks in advance!

I want to query the 5 countries with the highest percentage of their check-ins being at 'Starbucks'. Below is the example of the table I am working with and has many more rows not shown

https://lh4.googleusercontent.com/PzjmeVGnJwmOpAzmTmWbMM6v_8D-2otgiytXE8hfdxzHb3WnvhknQYmQBCYf3P6AOtOURnZq7-gsFegaaSDFN41LN0JLItN9OGvW9qSiVd-h59xEJej-EITqF7DJu2s3ig=w730

I can query counts of all check-ins in a specific country and counts of starbucks check-ins in a specific country separately.

all check-ins query

select loc_ctry, count(loc_ctry)
from Table_T
group by loc_ctry

starbucks check-ins query

select loc_ctry, count(loc_ctry)
from Table_T
where loc_name = 'Starbucks'
group by loc_ctry

However, I am having a hard time querying both at the same time to calculate the percentage. Any help will be appreciated!

EDITS: Thanks everyone for helping out. I have figured out the correct query as below:

SELECT   TOP 5 loc_ctry, cast((COUNT(CASE loc_name WHEN 'Starbucks' THEN 1 END)) as decimal) / count(*) as percents
FROM     Table_T
GROUP BY loc_ctry
ORDER BY percents DESC

CodePudding user response：

You could use a case expression inside the count to count the StarBucks check-ins and divide it by the total check-ins:

SELECT   TOP 5 loc_ctry, COUNT(CASE loc_name WHEN 'Starbucks' THEN 1 END) / COUNT(*)
FROM     table_t
GROUP BY loc_ctry
ORDER BY 2 DESC

CodePudding user response：

If you want find out top 5 Starbucks percentage try below query. And also you have 6 entries in image link and 2 for starbucks. Percentage should be 33.33. Count was cast to decimal for getting percentage calculation.

select top 5 * from 
(
select loc_ctry, ((TotalCountCountry/TotalCount)*100) Percentage from
(
 select loc_ctry, cast(count(loc_ctry) as decimal(18,2)) TotalCountCountry
,cast((select Count(*) from Table_T) as decimal(18,2)) TotalCount
from Table_T
where loc_name = 'Starbucks'
group by loc_ctry
) a
) b order by Percentage desc

CodePudding user response：

This is my first post here hope it will help you!

1st method: Universal:

SELECT TOP 5 loc_ctry, COUNT(*) * 100.0 / (SELECT count(*) FROM Table_T)
FROM Table_T
WHERE loc_name = 'Starbucks'
GROUP BY loc_ctry

2nd method: Using Over():

SELECT TOP loc_ctry, COUNT(*) * 100.0 / SUM(COUNT(*)) OVER()
FROM Table_T
WHERE loc_name = 'Starbucks'
GROUP BY loc_ctry

CodePudding user response：

You can use avg() to calculate the ratio and then just select the number you want:

select top (5) loc_ctry,
       avg(case when loc_name = 'Starbucks' then 1.0 else 0 end) as starbucks_ratio
from Table_T
group by loc_ctry
order by starbucks_ratio desc;