Write query on sql

user_8610603 0
Viewed 42

I have one monitoring table with client ID columns ID, last login date to application Time. I wrote a query to display the table at what time the clients had access to the system in the form: Time - Number of entries at this time - Client IDs.

Request:

select Time, count (*) as Quantity, group_concat (ClientID) from monitoring group by Time;

How do I write the following query? Display the table in the same form, only now it is necessary for each time when at least 1 client had access, display the id of all clients who did not have access at that time.

UPD.

+---------------------+-------------+----------------+
| Time                | Quantity    | ClientID       |                                                                                               
+---------------------+-------------+----------------+                                                                                                             
| 2018-06-14 15:51:03 |       3     | 311,240,528    |                                                                                                    
| 2018-06-14 15:51:20 |       3     | 314,312,519    |                                                                                                    
| 2019-01-14 06:00:07 |       1     | 359            |                                                                                                    
| 2019-08-21 14:30:04 |       1     | 269            |                                                                                                    
+---------------------+-------------+----------------+

These are the IDs of clients who currently had access. And you need to display the IDs of all clients who did not have access at that particular time That is, in this case:

+---------------------+-------------+-----------------------------+
| Time                | Quantity    | ClientID                    |                                                                                               
+---------------------+-------------+-----------------------------+                                                                                                            
| 2018-06-14 15:51:03 |           5 | 269,359,314,312,519         |                                                                                                    
| 2018-06-14 15:51:20 |           5 | 311,240,528,359,269         |                                                                                                    
| 2019-01-14 06:00:07 |           7 | 311,240,528,314,312,519,269 |                                                                                                    
| 2019-08-21 14:30:04 |           7 | 311,240,528,314,312,519,359 |                                                                                                    
+---------------------+-------------+-----------------------------+

It is advisable not to take into account the day and time, but only the year and month. But as soon as it comes out. Thanks.

subquery count datetime mysql sql
1 Answers

You can generate all possible combinations of clients and time with a cross join of two select distinct subqueries, and then filter out those that exist in the table with not exists. The final step is aggregation:

select t.time, count(*) as quantity, group_concat(c.clientid) as clientids
from (select distinct time from monitoring) t
cross join (select distinct clientid from monitoring) c
where not exists (
    select 1
    from monitoring m
    where m.time = t.time and m.clientid = c.clientid
)
group by t.time

It is unclear to me what you mean by the last sentence in the question. The above query would generate the results that you showed for your sample data.

user_106767160
Related