MySQL Where-clause with select

Question

I have a table that has three columns: visitations, country and user_id. I have a query to retrieve amount of all visitors per country and re-visitors per country. Now, I would like to alter my query so, that I get both amounts and a ratio of re-visitors per country (re-visitors / all visitors). I'm just learning MySQL and I feel that I don't know the tools to select all visitors and re-visitors and then use them in the ratio. Is there a way to do this in one query? Could someone help me with this? Thanks!

Here is my query for all visitors (and re-visitors if the # is removed)

SELECT sum(Visitations) AS "Amount", country
FROM E91
#WHERE Visitations > 1
GROUP BY Country
ORDER BY `Amount` DESC

Sample of the data, user is a revisitor if visitations is above 1:

user_id  |    country   |   visitations
---------|--------------|---------------
 beth123 |    Germany   |       4
 david78 |      USA     |       2
 matt23  |      UK      |       1
 ...

'for all visitors' - for all visitsI think , revisits will inflate. Mind you I am not clear what visitations means - please provide sample data and expected output as text in the question. — P.Salmon, Nov 01 '20 at 15:49
@P.Salmon you are correct, I had it wrong in my original query. I think Luuk's answer fixed this. And thank you for the tip, I will update the question. — coderfrombiz, Nov 01 '20 at 16:00

Luuk · Answer 1 · 2020-11-01T16:03:02.830

The where clause starts to filter records, leaving out records you do want to count for your total. To count the revisitors you can use CASE WHEN... END:

SELECT 
   sum(Visitations) AS "Amount", 
   sum(CASE WHEN Visitations > 1 THEN 1 ELSE 0 END) as "Re-Visitors",
   country
FROM E91
GROUP BY Country
ORDER BY `Amount` DESC

For further use you could do something like this:

SELECT 
   sum(Visitations) AS "Amount", 
   sum(CASE WHEN Visitations > 1 THEN 1 ELSE 0 END) as "Re-Visitors",
   sum(CASE WHEN Visitations > 1 THEN 1 ELSE 0 END) / sum(Visitations) as "X",
   country
FROM E91
GROUP BY Country
ORDER BY `Amount` DESC

or:

SELECT Amount, Re-visitors, "Re-visitors"/Amount, country
FROM (
   SELECT 
      sum(Visitations) AS "Amount", 
      sum(CASE WHEN Visitations > 1 THEN 1 ELSE 0 END) as "Re-Visitors",
      country
   FROM E91
   GROUP BY Country
   ORDER BY `Amount` DESC
   ) x

forpas · Answer 2 · 2020-11-01T16:18:46.067

The amount of visitors and re-visitors has nothing to do with the sum of the column visitations.
You can get the number of visitors (all user_ids who visited a country) by counting the number of user_ids for each country and the number of re-visitors by counting the number of user_ids for each country when the column visitations is greater than 1:

SELECT country,
       SUM(visitations > 1) AS revisitors,
       COUNT(*) AS visitors,
       AVG(visitations > 1) AS ratio
FROM E91
GROUP BY Country

MySQL Where-clause with select

2 Answers2