CODEWITHSUNDEEP
sqlsql-server
Sebastian
Sebastian

Reputation: 4811

Group by Date on a query along with Case Selection

I have a table called 'LoginSession' with below fields 

    id 
    CreatedDate
    UserAgent [which stores browser name as a string ]

I want to write a query to get the count of each browser for a specific period of time.

I used the below query 

        WITH BrowserShortCode_CTE AS(
        SELECT  UserAgent,
                case            
                    when Replace(UserAgent, ' ', '') like '%MSIE10.0%' then 'Internet Explorer 10'
                    when Replace(UserAgent, ' ', '') like '%MSIE9.0%' then 'Internet Explorer 9'
                    when Replace(UserAgent, ' ', '') like '%MSIE8.0%' then 'Internet Explorer 8'
                    when Replace(UserAgent, ' ', '') like '%MSIE7.0%' then 'Internet Explorer 7'
                else 'Others'
                end as BrowserName        
                FROM LoginSession 
                WHERE 
                CreatedDate >  '2017-01-01' 
                AND CreatedDate <  '2017-01-10' 
        )
        SELECT Count(0) as TotalCount, [BrowserName] FROM BrowserShortCode_CTE
        GROUP BY BrowserName
        ORDER BY TotalCount DESC

which returned the result as 

    Internet Explorer 10    100 
    Others                   95
    Internet Explorer 9       5
    Internet Explorer 8       2         
    Internet Explorer 7       1

How can I add a field CreatedDate to the group by section and make output like

    Date        BrowserName            Totalcount

i.e., the sum of browser wise usage details for each day.

Upvotes: 0

Views: 31

Answers (1)

beejm
beejm

Reputation: 2481

    WITH BrowserShortCode_CTE AS(
    SELECT  UserAgent,
            case            
                when Replace(UserAgent, ' ', '') like '%MSIE10.0%' then 'Internet Explorer 10'
                when Replace(UserAgent, ' ', '') like '%MSIE9.0%' then 'Internet Explorer 9'
                when Replace(UserAgent, ' ', '') like '%MSIE8.0%' then 'Internet Explorer 8'
                when Replace(UserAgent, ' ', '') like '%MSIE7.0%' then 'Internet Explorer 7'
            else 'Others'
            end as BrowserName,
            CAST(CreatedDate as DATE) as CreatedDate        
            FROM LoginSession 
            WHERE 
            CreatedDate >  '2017-01-01' 
            AND CreatedDate <  '2017-01-10' 
    )
    SELECT Count(0) as TotalCount, [BrowserName], CreatedDate FROM BrowserShortCode_CTE
    GROUP BY BrowserName, CreatedDate
    ORDER BY CreatedDate, TotalCount DESC

Upvotes: 1

Related Questions