Search code examples
sqlsql-servercountaggregate-functionsdistinct

Display only 1 person if the data is more than 1 in a day

I am struggling to write a query to display number of people check-in in a month if there is one person check-in more than 1 in a day. For example, if the person check-in 3 times in a day, and the data should display only 1.

This is my table

check_in table

id | checkin_date | user_id | office_id
1    13-12-2020     1         D01
2    13-12-2020     1         D02
3    13-12-2020     2         D01
4    14-12-2020     3         D01

Based on check_in table, I would like to produce result like this:

checkin_date | num_checkin
13-12-2020     2          
14-12-2020     1
Solution

  • Use count(distinct):

    select checkin_date, count(distinct user_id) as num_checkin
from mytable
group by checkin_date