Forum

write an sql query ...
 
Share:
Notifications
Clear all

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


Samantha A
Posts: 1
 Samantha A
Topic starter
(@Samantha A)
Joined: 1 month 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.

 

 

1 Reply
Deepak
Posts: 4
 Deepak
(@Deepak)
Joined: 1 month 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


Leave a reply

Author Name

Author Email

Title *

 
Preview 0 Revisions Saved
Share: