SQL - counting the number of logins each day, each user.
Consider the below table of results.
Login_Date Username ------------------------- 1/1/20 user-1 1/1/20 user-1 1/1/20 user-4 1/2/20 user-1 1/2/20 user-4 1/3/20 user-1 1/3/20 user-1 1/3/20 user-1 1/3/20 user-3
We may write a query as follows
SELECT Date-Used, Name AS license_Names FROM B, A WHERE A.License_Key = B.License_Key ORDER BY Date_Used ;
Which is fine, but what I need it to produce, is a list of dates, where for every date it gives me a list of druggies that logged in that day, along with how numerous times they logged in. I have looked each over to try and find a terse result to this, but It's been nothing but dead ends.
The result I want published would look like the following
Login_Date Username # Num of Times logged in -------------------------------------------------- 1/1/20 user-1 2 1/1/20 user-4 1 1/2/20 user-1 1 1/2/20 user-4 2 1/3/20 user-1 3 1/3/20 user-3 1
Any help would be much appreciated, this is where I struck
EDIT
Here are the respective tables, to avoid confusion:
Table A Columns:
--------------------------
ID License-Key Name
Table B Columns:
------------------------------
ID Date_Used License_Key
As per a comment request, all of the results therefore far have handed the same result on the factual tables (although the SQL Swindle works for all of them, so.?)
The results I am getting are as follows.
Login_Date User_Name Count (or NumLogins for the other solution) --------------------------------------------------------------------- 01-01-20 user-1 1 01-01-20 user-1 1 01-01-20 user-4 1 01-02-20 user-1 1 01-02-20 user-1 1 01-02-20 user-1 1 01-02-20 user-1 1
Etc. So it looks like the group by command in all of the answers therefore far simply do. nothing?? Veritably confusing.
You have to use a GROUP In your query, with a COUNT (*) aggregated function. Since Date_Used is a DATETIME column, it can contain the DATE part but also the TIME part, so you have to prize just the DATE part using DATE () function
SELECT DATE(Date_Used), Name AS license_Names, COUNT(*) AS Nologins FROM B INNER JOIN A ON A.License_Key = B.License_Key GROUP BY DATE(Date_Used), Name ORDER BY DATE(Date_Used), Name
To find how many users logged in, you will need to have a table that records user logins. Assuming you have a table named user_logins
that has columns user_id
and login_time
, you can use the following SQL query to count the number of unique users who logged in:
SELECT COUNT(DISTINCT user_id) AS num_users_logged_in FROM user_logins;
Explanation:
COUNT(DISTINCT user_id)
counts the number of unique values in theuser_id
column.AS num_users_logged_in
aliases the column tonum_users_logged_in
for easier reference.FROM user_logins
specifies the table to query.
This query should return the number of unique users who logged in, based on the records in the user_logins
table
Very useful