Distinct days, total number

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

from Tablename
Group by UserName

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]

try this


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

from Tablename
Group by UserName

;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

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

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

;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

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

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