Jibin Mathew Jibin Mathew - 9 months ago 32
SQL Question

Get the Sum of a count with group by in SQL

I am trying to present the data as a sum on group by basis and the expected output is something like

Windows-10 yyy
Windows-8 xxx
Windows-7 abc


In the table
LOGINHISTORYTABLE
, I have a column
useragent
that stores the value of OS and browser in a special format like

Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 10.0; WOW64; Trident/8.0;

Mozilla/5.0 (Windows NT 6.3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/56.0.2924.87 Safari/537.36 etc


So my plan is that remove all space in this column so values becomes

Mozilla/4.0(compatible;MSIE7.0;WindowsNT10.0;WOW64;Trident/8.0;
Mozilla/5.0(WindowsNT6.3;Trident/7.0;Touch;MAARJS;rv:11.0)likeGecko etc


And assume if the column contains WindowsNT10.0 I count it as a Windows 10 instance

If WindowsNT6.3 is present, I count it as a Windows 8 instance etc

So I am able to find the count with this query:

SELECT
COUNT(d.UserAgent) as countname,
Replace(d.UserAgent, ' ', ' ') as name
FROM
LOGINHISTORYTABLE as d
WHERE
d.CreatedDate > '2017-02-08'
AND d.CreatedDate < '2017-02-09'
AND (Replace(d.UserAgent, ' ', '') like '%WindowsNT10.0%'
OR Replace(d.UserAgent, ' ', '') like '%WindowsNT6.3%')
GROUP BY
d.UserAgent


From this now I want to find SUM for each group

I tried

SELECT SUM(countname) as TotalCount , 'Windows 10' as OS
FROM
(
SELECT COUNT(d.UserAgent) as countname,
Replace(d.UserAgent, ' ', ' ') as name
FROM LOGINHISTORYTABLE as d
WHERE d.CreatedDate> '2017-02-08' AND d.CreatedDate < '2017-02-09' AND
(Replace(d.UserAgent, ' ', '') like '%WindowsNT10.0%'
OR
Replace(d.UserAgent, ' ', '') like '%WindowsNT6.3%')
GROUP BY d.UserAgent
)a


But this returns overall sum only . How can i make this sum as group by and if possible how can i use the custom labels for each group

Answer Source

Try

WITH TranslatedOS_CTE AS (
SELECT  UserAgent,CreatedDate,
        case when UserAgent like '%NT 6.3%' then 'Windows8'
        when UserAgent like '%Windows NT 10.0%' then 'Windows10'
        else UserAgent
        end as OS        
        FROM LOGINHISTORYTABLE 
)

Select Count(0), [OS] from TranslatedOS_CTE
WHERE CreatedDate>  '2017-02-08' AND CreatedDate < '2017-02-09'
group by OS