SQL Masters! I need your help.

[Zucarita9000]

This isn't the first time I asked for help on SQL statements in this board, but I preferred to open a new topic due to the lenght of my new request.

I need to create some reports from my db, wich is on a MySQL server ver. 3.23.54. My app is written in ASP. The db structure is quite simple, but effective.

Table: Users
Fields: Username, Country, DateTime (using 0000-00-00 00:00:00 format)

Table: UserLogins
Fields: Username, DateTime (using 0000-00-00 00:00:00 format)

Reports I have to create:

1. Top 10 of the most active users (no. of logins). This should bring up a recordset that will allow me to create a list such as this:

1. john32 (34 logins)
2. wacky (22 logins)
...
10. mikeBe (11 logins)

2. Users registered today. This one is easy. No help needed.
3. Users registered in the past seven days. Should bring up all the users that registered in the site during the past week.
4. Users by country. This one should create something like this:

USA (33 users)
Mexico (23 users)
Brazil (10 users)
...

Any help will be very much appreciated.

 

[Zucarita9000]

Reports 2 & 3 are done.

 

[MrChad]

1.
select top 10 Username, count(*) As NumLogins
from UserLogins
group by Username
order by NumLogins desc

3.
select *
from Users
where DateTime >= datesub(curdate(), interval 7 day)

4.
select Country, count(*) as UserCount
from Users
group by Country
order by Country

 

[Zucarita9000]

Great!!!! Thanks a lot! you're da man.

 

[Zucarita9000]

I got another one for you!

I have another table called Downloads. It's used to keep track of the catalogs downloaded by our users. The table has the following fields:

Username, Catalog, DateTime

I need to create a report to display the no. of downloads by Country, so I would need to link the Users and the Downloads tables in order to display something like this:

USA (445 downloads)
Mexico (389 downloads)
Brazil (445 downloads)


--
FYI: all your previous statements work perfectly!

 

[MrChad]

select Country, count(*) as DownloadCount
from Users, Downloads
where Users.Username = Downloads.Username
group by Country

 

[Zucarita9000]

Man that was fast! and accurate also. Works like a charm, thanks a lot!.