SQL grouping unique occurrences by date-CodePudding

I am looking for a MySQL query to count how many people visited a web-app. I have a database storing information about visitors, they type and when they tried to login:

 ----- ----------- -------- --------- --------------------- 
| id  | person_id | area   | success | date_created        |
 ----- ----------- -------- --------- --------------------- 
| 762 |       100 | client |       1 | 2022-06-28 09:22:35 |
| 763 |       100 | client |       1 | 2022-06-28 11:22:35 |
| 764 |       100 | client |       1 | 2022-06-28 14:22:35 |
| 765 |       102 | client |       1 | 2022-06-28 15:04:43 |
| 766 |       101 |   user |       1 | 2022-06-28 16:04:43 |
| 767 |       100 | client |       1 | 2022-06-29 15:07:29 |
| 768 |       101 |   user |       1 | 2022-06-30 09:18:41 |
| 769 |       100 | client |       1 | 2022-07-01 09:24:46 |
| 770 |       101 |   user |       1 | 2022-07-01 09:25:59 |
| 771 |       102 | client |       1 | 2022-07-01 14:17:57 |
| 772 |       103 | client |       1 | 2022-07-01 15:20:14 |
| 773 |       100 | client |       1 | 2022-07-02 13:01:49 |
| 774 |       100 | client |       1 | 2022-07-03 09:06:00 |
| 775 |       102 | client |       0 | 2022-07-03 12:11:23 |
| 776 |       103 | client |       1 | 2022-07-03 12:41:43 |

I need to parse a date_created to date, sum unique occurrences of person_id if the are is client and sum by date.

Wouldn't have a problem to do it in other programming language but I struggle with building the query in SQL. Need to do it on the box and can't really get much installed there.

The expected result:

 ------------ -------------------- 
| date       | successful_clients |
 ------------ -------------------- 
| 2022-06-28 |                  2 |
| 2022-06-29 |                  1 |
| 2022-06-30 |                  0 |
| 2022-07-01 |                  3 |
| 2022-07-02 |                  1 |
| 2022-07-03 |                  2 |

What would be efficient SQL query?

CodePudding user response：

Use conditional aggregation:

SELECT DATE(date_created) date,
       COUNT(DISTINCT CASE WHEN success AND area = 'client' THEN person_id END) successful_clients 
FROM tablename
GROUP BY date
ORDER BY date;

See the demo.