February 25, 2011 at 7:01 pm
hi
i have a table like this
name data type description
userid integer User ID
username text User name
logindate date Date the user logged in
how to show the user name and date of a user who logged in more than once during any given day. Do not show UserID in the result.
thanks
February 26, 2011 at 12:32 am
must be homework :ermm:
just select the columns you need and add a count(*) as nLogins
Convert your datetime column so the startime is converted to 00:00:00.000
( or convert it to date datatype since you're on sql2008)
and group by that column.
With the group by you can use the having clause to directly filter on the count(*) result.
Darn, it took me more time to type the above that it would have taken me to write the query :Whistling: :StubbornGuyWhoThinksHeKnowsThisOne:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 26, 2011 at 8:54 am
daveriya (2/25/2011)
hii have a table like this
name data type description
userid integer User ID
username text User name
logindate date Date the user logged in
how to show the user name and date of a user who logged in more than once during any given day. Do not show UserID in the result.
thanks
Better begin thinking about altering the username column's data type,
from Books On Line (BOL)
Important:
ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.
February 26, 2011 at 10:09 am
This is not working
select username from logs group by logindate having COUNT(*) > 1
logindate is already date datatype,
its gives me error like
Column 'logs.username' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
February 26, 2011 at 10:13 am
hi
select convert(varchar(20),username), logindate
from logs
group by userid,convert(varchar(20),username),logindate
having count(*) > 1
i tried this,but dont understand group by it is necessary to have 3 column in this query
February 26, 2011 at 10:59 am
Always good to post the thing you tried.
You must tell sql what you want.
You want per date usernames and the number of logins.
So this is the kind of query you should produce.
if username is a varchar col,this will work
select logindate, username, count(*) nLogins
from logs
group by logindate, username
having COUNT(*) > 1
/* just to be able to see things correct */
order by logindate, username
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 26, 2011 at 12:01 pm
what about particular day,i should add where condition.
thanks
February 26, 2011 at 11:11 pm
daveriya (2/26/2011)
what about particular day,i should add where condition.thanks
Yes, but we careful if the LoginDate column contains times as well as dates:
SELECT LoginDate,
UserName,
nLogins = COUNT(*)
FROM dbo.logs
WHERE LoginDate >= '2009-04-03 00:00:00'
AND LoginDate < '2009-04-04 00:00:00'
GROUP BY
LoginDate,
UserName
HAVING COUNT(*) > 1
ORDER BY
LoginDate,
UserName
;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 27, 2011 at 1:08 am
daveriya (2/26/2011)
what about particular day,i should add where condition.thanks
It depends on your needs.
If you want a total overview, there is no need.
Probably you'll only want e.g. last weeks info, so you add a where clause providing your start date.
declare @thestartdate date
set @thestartdate = '2011-02-01'
....
where yourdatecol >= @thestartdate
group by ...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply