Clash Royale CLAN TAG#URR8PPP

Count per month if unique

I am trying to get a SQL query to count personid unique for the month, is a 'Returning' visitor unless they have a record of 'New' for the month as well.

personid

month | personid | visitstat
---------------------------------
January john new
January john returning
January Bill returning


So the query I'm looking for should get a count for each unique personid that has "returning" unless a "new" exists for that personid as well - in this instance returning a count of 1 for

count

January Bill returning

January Bill returning

because john is new for the month.

The query I've tried is

SELECT COUNT(distinct personid) as count FROM visit_info WHERE visitstat = 'Returning' GROUP BY MONTH(date) ORDER BY date

SELECT COUNT(distinct personid) as count FROM visit_info WHERE visitstat = 'Returning' GROUP BY MONTH(date) ORDER BY date

Unfortunately this counts "Returning" even if a "New" record exists for the person in that month.

Thanks in advance, hopefully I explained this clearly enough.

SQL Database Image

Chart of Data

2 Answers
2

You already wrote the "magic" word yourself, "exists". You can use exactly that, a NOT EXISTS and a correlated subquery.

NOT EXISTS

SELECT count(DISTINCT vi1.personid) count
FROM visit_info vi1
WHERE vi1.visitstat = 'Returning'
AND NOT EXISTS (SELECT *
FROM visit_info vi2
WHERE vi2.personid = vi1.personid
AND year(vi2.date) = year(vi1.date)
AND month(vi2.date) = month(vi1.date)
AND vi2.visitstat = 'New')
GROUP BY year(vi1.date),
month(vi1.date)
ORDER BY year(vi1.date),
month(vi1.date);


I also recommend to include the year in the GROUP BY expression, as you otherwise might get unexpected results when the data spans more than one year. Also only use expressions included in the GROUP BY clause or passed to an aggregation function in the ORDER BY clause. MySQL, as opposed to virtually any other DBMS, might accept it otherwise, but may also produce weird results.

GROUP BY

GROUP BY

ORDER BY

I also faced one of the same scenarios I was dealing with a database. The possible way I did was to use group by with having clause and a subquery.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.