Distinct days, total number

I would like to find out how many distinct days a UserName is registered and also total number each Username is registered.
Id(int), UserName (varchar(50)), DateCreated (datetime)
Id=11, UserName="John", DateCreated="20.12.2011 10:00"
Id=12, UserName="John", DateCreated="21.12.2011 11:55"
Id=13, UserName="John", DateCreated="21.12.2011 11:59"
Id=14, UserName="Bill", DateCreated="22.12.2011 10:00"
The result should be: Username='John', NumberOfDays=2, Total=3
                                Username='Bill', NumberOfDays=1, Total=1
Id 12 and 13 have the same date so it is counted only once for NumberOfDays.
How is best to do this?

johnkainnAsked:
Who is Participating?
 
LowfatspreadCommented:
and if datecreated is null.... what effect do you want that to have on the count(distinct date)....

then standard sql one of it being ignored... or something else...
0
 
Pratima PharandeCommented:
Select UserName , Count (distinct Day(DateCreated)) as NumberOfDays , Count(Id) as Total

from Tablename
Group by UserName
0
 
MadRacer1Commented:
HI johnkainn,

Please execute below code, it will give you expected result

SELECT [UserName]
,count(distinct convert(nvarchar(12),[DateCreated],102)) as NumberOfDays
,COUNT([Id]) Total
FROM TableName                    --Replace your table name here
group by [UserName]
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Pratima PharandeCommented:
try this


Select UserName , Count (distinct CONVERT(VARCHAR(10), DateCreated, 111)) as NumberOfDays , Count(Id) as Total

from Tablename
Group by UserName
0
 
LowfatspreadCommented:
;with cte as (
select username,convert(char(8),datecreated,112) as D
from yourtable
)
select username,count(distinct d) as Numberofdays,count(*) as [total]
from cte
group by username
order by username
0
 
Gerard68Commented:
Try this code below. I have a select statement in the from clause.


create table counttest (
  id int not null,
  username varchar(50) not null,
  datecreated datetime)
 
  go


insert into counttest (id,username, datecreated)
values(11, 'John',convert(datetime,'2011-12-20 21:03:00',20))
,(12, 'John',convert(datetime,'2011-12-21 21:03:00',20))
,(13, 'John',convert(datetime,'2011-12-21 21:03:45',20))
,(15, 'Bill',convert(datetime,'2011-12-22 21:03:00',20))


select username, count(t.NumberofDays) as NumberOfDays, SUM(t.NumberofDays) as Total
from
(select username, count(username) as NumberofDays
from counttest
group by username, CONVERT(varchar, datecreated,110)) as t
group by username

drop table counttest
go
0
 
johnkainnAuthor Commented:
Sorry I meant to add as well that there can be more then 1 TypeId.
I would like to get also a row for each Type. See modified sample data below.

Id(int), UserName (varchar(50)), DateCreated (datetime),TypeId (int)
Id=11, UserName="John", DateCreated="20.12.2011 10:00",TypeId=1
Id=12, UserName="John", DateCreated="21.12.2011 11:55",TypeId=1
Id=13, UserName="John", DateCreated="21.12.2011 11:59", TypeId=1
Id=14, UserName="Bill", DateCreated="22.12.2011 10:00", TypeId=1
Id=15, UserName="John", DateCreated="22.12.2011 10:00", TypeId=2
Id=16, UserName="John", DateCreated="22.12.2011 11:00",TypeId=2
The result should be: Username='John', NumberOfDays=2, Total=3, TypeId=1
                                Username='John', NumberOfDays=1, Total=2, TypeId=2
                                Username='Bill', NumberOfDays=1, Total=1, TypeId=1
0
 
LowfatspreadCommented:
;with cte as (
select username,convert(char(8),datecreated,112) as D,typeid
from yourtable
)
select username,count(distinct d) as Numberofdays,count(*) as [total],typeid
from cte
group by username,typeid
order by username,typeid
0
 
Scott PletcherSenior DBACommented:
You don't really need a CTE for this; a standard query will work.

SELECT
    Username,
    COUNT(DISTINCT CAST(DateCreated AS int)) AS NumberOfDays,
    COUNT(DateCreated) AS Total,
    Type
FROM tablename
GROUP BY
    Username,
    Type
0
 
Scott PletcherSenior DBACommented:
[If it's possible for DateCreated to be NULL, you might want to use COUNT(*) instead of COUNT(DateCreated).  But I figured if DateCreated is NULL you wouldn't want to count it at all.]
0
 
Scott PletcherSenior DBACommented:
Yes, to clarify:

NULL will be ignored by the COUNT(DISTINCT column) and the COUNT(column).

NULL will be counted by COUNT(*).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.