SQL - counting number of logins per day, per user

Question

I have a query that produces a table like the following:

Login_Date     Username
----------------------------------------------
1/1/10         user-1
1/1/10         user-1
1/1/10         user-2
1/2/10         user-1
1/2/10         user-2
1/3/10         user-1
1/3/10         user-1 
1/3/10         user-1
1/3/10         user-3

The query looks like the following:

SELECT Date_Used, Name AS licenseNames 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 users that logged in that day, along with how many times they logged in. I've looked all over to try and find a concise solution to this, but It's been nothing but dead ends.

The result I want published would look like the following:

Login_Date    Username    # of Times logged in
--------------------------------------------------
1/1/10       user-1       2
1/1/10       user-2       1
1/2/10       user-1       1
1/2/10       user-2       2
1/3/10       user-1       3
1/3/10       user-3       1

Any help would be much appreciated, this is driving me insane.

EDIT

Here are the respective tables, to avoid confusion:

Table A Columns:

--------------------------
ID   License_Key    Name

Table B Columns:

------------------------------
ID    Date_Used     License_Key

EDIT 2

As per a comment request, all of the solutions thus far have provided the same result on the actual tables (although the SQL Fiddle works for all of them, so..?)

The results I'm getting are as follows:

Login_Date     UserName     Cnt (or NumLogins for the other solution)
---------------------------------------------------------------------
01-01-10       user-1        1
01-01-10       user-1        1
01-01-10       user-2        1
01-02-10       user-1        1
01-02-10       user-1        1
01-02-10       user-1        1
01-02-10       user-1        1

etc. etc. So it looks like the group by command in all of the answers thus far simply do.. nothing?? Very confusing...

Answer 1

You have to use a GROUP BY 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 extract just the DATE part using DATE() function:

SELECT
  DATE(Date_Used),
  Name AS licenseNames,
  COUNT(*) AS logins
FROM
  B INNER JOIN A
  ON A.License_Key = B.License_Key
GROUP BY
  DATE(Date_Used),
  Name
ORDER BY
  DATE(Date_Used),
  Name

Answer 2

What you need to implement here is the COUNT() aggregate function. You can use it with a GROUP BY statement, and it will count the number of rows that exist within your group.

In your case, you want to group by user and date. I also recommend you use a join instead:

SELECT date_used, name AS licenseNames, COUNT(*) AS numLogins
FROM B
JOIN A ON a.license_key = b.license_key
GROUP BY date_used, name
ORDER BY date_used;

EDIT

Here is an SQL Fiddle. The results look a little different form yours, but I think you made a mistake as user-2 only logged in once on 01/02/10 according to the table you have.