Group user agents by browser

Question

I have a table with each row containing data about a page load by a user. One of the fields is the user agent for that hit. How can I group the results by the browser? I can group by common user agents:

SELECT useragent, COUNT(useragent) AS c
FROM hitstable
GROUP BY useragent
ORDER BY c DESC

But I want to group by browser, regardless of version or other data that changes. I'd like it to output a list listing the browsers by how common they are.

How can I do this?

score 4 · Answer 1 · answered Nov 04 '16 at 11:43

I found it helpful to use a MySQL CASE for parsing the browser, operating system, and bit architecture. The data ($_SERVER['HTTP_USER_AGENT']) was inserted via PHP into a MySQL table. Maybe you find the following code helpful. It also renders some Font Awesome icons depending on the UserAgent.

The SELECT below groups by the browser alias. The same can be done for OS and BitArchitecture of course.

SELECT 
    CASE
        WHEN `UserAgent` LIKE '%Mac%OS%' THEN '<i class="fa fa-apple"></i> Mac OS X'
        WHEN `UserAgent` LIKE '%iPad%' THEN '<i class="fa fa-apple"></i> iPad'
        WHEN `UserAgent` LIKE '%iPod%' THEN '<i class="fa fa-apple"></i> iPod'
        WHEN `UserAgent` LIKE '%iPhone%' THEN '<i class="fa fa-apple"></i> iPhone'
        WHEN `UserAgent` LIKE '%imac%' THEN '<i class="fa fa-apple"></i> mac'
        WHEN `UserAgent` LIKE '%android%' THEN '<i class="fa fa-android"></i> android'
        WHEN `UserAgent` LIKE '%linux%' THEN '<i class="fa fa-linux"></i> linux'
        WHEN `UserAgent` LIKE '%Nokia%' THEN 'Nokia'
        WHEN `UserAgent` LIKE '%BlackBerry%' THEN 'BlackBerry'
        WHEN `UserAgent` LIKE '%win%' THEN
            CASE
                WHEN `UserAgent` LIKE '%NT 6.2%' THEN '<i class="fa fa-windows"></i> Windows 8'
                WHEN `UserAgent` LIKE '%NT 6.3%' THEN '<i class="fa fa-windows"></i> Windows 8.1'
                WHEN `UserAgent` LIKE '%NT 6.1%' THEN '<i class="fa fa-windows"></i> Windows 7'
                WHEN `UserAgent` LIKE '%NT 6.0%' THEN '<i class="fa fa-windows"></i> Windows Vista'
                WHEN `UserAgent` LIKE '%NT 5.1%' THEN '<i class="fa fa-windows"></i> Windows XP'
                WHEN `UserAgent` LIKE '%NT 5.0%' THEN '<i class="fa fa-windows"></i> Windows 2000'
                ELSE '<i class="fa fa-windows"></i> Windows'
            END      
        WHEN `UserAgent` LIKE '%FreeBSD%' THEN 'FreeBSD'
        WHEN `UserAgent` LIKE '%OpenBSD%' THEN 'OpenBSD'
        WHEN `UserAgent` LIKE '%NetBSD%' THEN 'NetBSD'
        WHEN `UserAgent` LIKE '%OpenSolaris%' THEN 'OpenSolaris'
        WHEN `UserAgent` LIKE '%SunOS%' THEN 'SunOS'
        WHEN `UserAgent` LIKE '%OS/2%' THEN 'OS/2'
        WHEN `UserAgent` LIKE '%BeOS%' THEN 'BeOS'
        ELSE 'Unknown'
    END AS `OS`,
    CASE
        WHEN `UserAgent` LIKE '%edge%'THEN '<i class="fa fa-edge"></i> Edge'
        WHEN `UserAgent` LIKE '%MSIE%' THEN '<i class="fa fa-internet-explorer"></i> Internet Explorer'
        WHEN `UserAgent` LIKE '%Firefox%' THEN '<i class="fa fa-firefox"></i> Mozilla Firefox'
        WHEN `UserAgent` LIKE '%Chrome%' THEN '<i class="fa fa-chrome"></i> Google Chrome'
        WHEN `UserAgent` LIKE '%Safari%' THEN '<i class="fa fa-safari"></i> Apple Safari'
        WHEN `UserAgent` LIKE '%Opera%' THEN '<i class="fa fa-opera"></i> Opera' 
        WHEN `UserAgent` LIKE '%Outlook%' THEN 'Outlook' 
        ELSE 'Unknown'
    END AS `Browser`,
    CASE
        WHEN `UserAgent` LIKE '%WOW64%' THEN '64 bit'
        WHEN `UserAgent` LIKE '%x64%' THEN '64 bit'
        ELSE '32 bit'
    END AS `BitArchitecture`
FROM `Logs`
GROUP BY `Browser`;

score 1 · Answer 2 · answered Aug 19 '13 at 14:36

1

You can find SQL statement (MySQL and SQL Server) for sort distinct user-agent/version on group http user agent sql. It's based from RegExp and switch case SQL from popular browsers.

answered Aug 19 '13 at 14:36

immobiluser

349
1
2
12

score 0 · Answer 3 · answered May 12 '12 at 01:05

0

see php's get_browser() function to identify it from the user agent string. You'll need to loop over all user agent strings, counting the results of each.

answered May 12 '12 at 01:05

goat

31,486
7
73
96

score 0 · Answer 4 · answered May 12 '12 at 01:12

Two approaches come to mind.

1) Add a column that stores only the browser name. This will make it very easy to group by browser at the expense of making your table larger. You can use the get_browser() function suggested by Chris.

2) Query results for each browser individually using a REGEXP or a LIKE query (LIKE "%chrome/%") and then union the results together. This won't require any additional space, but it makes your query more problematic.

Group user agents by browser

4 Answers4