CODEWITHSUNDEEP
sql
callisto
callisto

Reputation: 5083

How do I aggregate this in SQL

I've got a table with these fields: 

UserLogonAuditID, Username, LogonDate FROM dbo.UserLogonAudit

Querying with this:

SELECT  Username ,
        DATEPART(Year, LogonDate) [Year] ,
        DATENAME(MONTH, LogonDate) [Month] ,
        COUNT(1) [LoginCount] ,
        CAST(DATEPART(Year, LogonDate) AS VARCHAR(4))
        + CAST(DATEPART(Month, LogonDate) AS VARCHAR(2)) AS [YearMonth] ,
        CAST(CAST(DATEPART(Year, LogonDate) AS VARCHAR(4)) + '-'
        + CAST(DATEPART(Month, LogonDate) AS VARCHAR(2)) + '-1' AS datetime) AS [MonthStart]
FROM    UserLogonAudit
GROUP BY DATEPART(Year, LogonDate) ,
        DATENAME(Month, LogonDate) ,
        DATEPART(Month, LogonDate) ,
        Username
HAVING  Username = '[email protected]'

results in this:

> Username           Year        Month  LoginCount  YearMonth   MonthStart
> [email protected]   2010    February    1   20102   2010-02-01 00:00:00.000
> [email protected]   2010    June       1    20106   2010-06-01 00:00:00.000
> [email protected]   2011    November    1   201111  2011-11-01 00:00:00.000
> [email protected]   2012    April     115   20124   2012-04-01 00:00:00.000
> [email protected]   2012    January     1   20121   2012-01-01 00:00:00.000

I would like to get this result

> Username           Year        Month  LoginCount  YearMonth   MonthStart
> [email protected]   2010    February    1   20102   2010-02-01 00:00:00.000
> [email protected]   2010    June       1    20106   2010-06-01 00:00:00.000
> [email protected]   2011    November    1   201111  2011-11-01 00:00:00.000
> [email protected]   2012    April     115   20124   2012-04-01 00:00:00.000
> [email protected]   2012    January     1   20121   2012-01-01 00:00:00.000    > 
> [email protected]   2010    February    3   20102   2010-02-01 00:00:00.000
> [email protected]   2010    June       2    20106   2010-06-01 00:00:00.000
> [email protected]   2011    November    0   201111  2011-11-01 00:00:00.000
> [email protected]   2012    April     190   20124   2012-04-01 00:00:00.000
> [email protected]   2012    January     6   20121   2012-01-01 00:00:00.000

So, for all users.

I can loop through the usernames, but feel that is not the best way.

Upvotes: 2

Views: 73

Answers (1)

hkutluay
hkutluay

Reputation: 6944

try to remove HAVING Username = '[email protected]' clause from your sql

Upvotes: 2

Related Questions