I'm helping with a local food pantry and shifting their data mgmt from google sheets to airtable. They register clients in a 1-time data entry task, then each time that client visits, they record that visit. So I have a Clients table and ClientVisits table. One client can obviously visit many times. Sometimes twice in a month.
I am feeding this data into interfaces for the users.
Each month the food pantry needs to run a report for:
- Number of visits
- Number of unique clients
- Some other demographic info
Number of visits is quite easy of course. How do I get the number of unique clients counted? In MySQL or SQL Server this is what I would want to do:
SELECT COUNT(DISTINCT clientid) FROM ClientVisits
WHERE VisitDate >= '12/1/2022'
AND VisitDate <= '12/31/2022';