Need quick SQL solution
-
I have a database supporting a web page that logs each user login. I want to count each user's logins and insert the result into a new table, preferably using a stored procedure. For example:
select count(*) from tLogins where userid=1
will display a single user's number of logins.
I want to run this command, cycle userid from 1 to 20, and insert into a new table the values userid, numlogins
I know this is probably simple but I can't seem to figure it out. Please help!
-
select min(userid), count(*) from tLogins
where userid>=1 and userid<=20
group by userid
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Forgot to add. Create the new table first and add an INSERT INTO newtable before the SELECT.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Frank;
Would not that be "select userid, count(*) ...", without the min() function?
Mark -
Result would be the same, as MIN() is not needed
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks!! It worked perfectly.
One more question, how can I sort by the count(*) ?
it says 'no column name' in the count column, so I can't list it
in an 'order by' statement. Is there anyway to do this? I want to order highest to lowest.
Thanks
-
select min(userid), count(*) Logins from tLogins
where userid>=1 and userid<=20
group by userid ORDER BY 2 DESC
You can sort it by column Number or if you want to specify names
SELECT * FROM(select min(userid), count(*) Logins from tLogins
where userid>=1 and userid<=20
group by userid) A ORDER BY Logins DESC
Regards,
gova
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply