Forum

Forums
SQL
Other Documents
write an sql query ...
 
Share:
Notifications
Clear all

write an sql query to find out how many users logged in

 
    Last Post
RSS

Posts: 1
 Samantha A
Guest
Topic starter
May 23, 2022 10:17 am
(@Samantha A)
New Member
Joined: 3 years ago

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.

 

 

3 Replies
Posts: 4
 Deepak
Guest
May 23, 2022 10:24 am
(@Deepak)
New Member
Joined: 3 years ago

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
Reply
Posts: 69
 Sai Ram
Guest
Feb 17, 2023 5:37 pm
(@Sai Ram)
Trusted Member
Joined: 5 years ago

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 the user_id column.
  • AS num_users_logged_in aliases the column to num_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

This post was modified 2 years ago by SQL Admin
Reply
Posts: 1
 KV Course
Mar 03, 2023 2:45 pm
(@kvcourseonline)
New Member
Joined: 2 years ago

Very useful

Reply

Leave a reply

Author Name

Author Email

Title *

Preview 0 Revisions Saved
Forum Jump:
  Previous Topic
Next Topic  
Share: