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?
>Solution :
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.