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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pratima PharandeCommented:
Select UserName , Count (distinct Day(DateCreated)) as NumberOfDays , Count(Id) as Total

from Tablename
Group by UserName
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]
Pratima PharandeCommented:
try this


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

from Tablename
Group by UserName
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

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
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
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
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
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
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.]
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...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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(*).
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.