Notifications

Clear all

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

Last Post

Posts: 1

Samantha A

Guest

Topic starter

May 23, 2022 10:17 am

(@Samantha A)

New Member

Joined: 1 year 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: 1 year 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

Posts: 32

Sai Ram

Guest

Feb 17, 2023 5:37 pm

(@Sai Ram)

Eminent Member

Joined: 3 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 7 months ago by SQL Admin

Posts: 1

KV Course

Mar 03, 2023 2:45 pm

(@kvcourseonline)

New Member

Joined: 7 months ago

Very useful