# 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.
The result should be: Username='John', NumberOfDays=2, Total=3
Id 12 and 13 have the same date so it is counted only once for NumberOfDays.
How is best to do this?

###### Who is Participating?

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.

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

from Tablename
Commented:
HI johnkainn,

Please execute below code, it will give you expected result

,count(distinct convert(nvarchar(12),[DateCreated],102)) as NumberOfDays
,COUNT([Id]) Total
FROM TableName                    --Replace your table name here
Commented:
try this

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

from Tablename
Commented:
;with cte as (
from yourtable
)
select username,count(distinct d) as Numberofdays,count(*) as [total]
from cte
Commented:
Try this code below. I have a select statement in the from clause.

create table counttest (
id int not null,
datecreated datetime)

go

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
from counttest
group by username, CONVERT(varchar, datecreated,110)) as t

drop table counttest
go
Author 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)
The result should be: Username='John', NumberOfDays=2, Total=3, TypeId=1
Commented:
;with cte as (
from yourtable
)
select username,count(distinct d) as Numberofdays,count(*) as [total],typeid
from cte
Senior DBACommented:
You don't really need a CTE for this; a standard query will work.

SELECT
COUNT(DISTINCT CAST(DateCreated AS int)) AS NumberOfDays,
COUNT(DateCreated) AS Total,
Type
FROM tablename
GROUP BY
Type
Senior 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.]
Commented:
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