How to select count data by group by regex in MYSQL?

Question

I have a table that saves the user geolocation information. How can I count the number of users with same device by creating a regex?

Query

SELECT userAgent, COUNT(DISTINCT userAgent) as countVisitor 
FROM geolocation 
WHERE last_update BETWEEN '2020-11-01' AND '2020-12-01' 
GROUP BY userAgent
ORDER BY last_update ASC

Result

userAgent                                            | countVisitor
Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:82.0) G...| 1
Mozilla/5.0 (iPad; CPU OS 11_0 like Mac OS X) Appl...| 1
Mozilla/5.0 (iPad; CPU OS 11_4 like Mac OS X) Appl...| 1
Mozilla/5.0 (iPhone; CPU iPhone OS 11_0 like Mac O...| 1
Mozilla/5.0 (Linux; Android 7.0; SM-G892A Build/NR...| 1

Desired Result

userAgent   | countVisitor
Ubuntu      | 1
iPad        | 2
iPhone      | 1
Android     | 1

And what is the rule for extracting the user agent? It is not obvious. — Gordon Linoff
– Gordon Linoff, Commented Nov 18, 2020 at 3:27

Gordon Linoff · Accepted Answer · 2020-11-18 03:29:12Z

1

Hmmm . . . I don't see a pattern that can really be used. You could use a CASE expression:

SELECT (CASE WHEN userAgent LIKE '%Ubuntu%' THEN 'Ubuntu'
             WHEN userAgent LIKE '%iPad%' THEN 'iPad'
             WHEN userAgent LIKE '%iPhone%' THEN 'iPhone'
             WHEN userAgent LIKE '%Android%' THEN 'Android'
             ELSE 'Other'
        end) as platform,COUNT(DISTINCT ipAddress) as countVisitor 
FROM geolocation 
WHERE last_update BETWEEN '2020-11-01' AND '2020-12-01' 
GROUP BY platform
ORDER BY MIN(last_update) ASC

answered Nov 18, 2020 at 3:29

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Grogu · Accepted Answer · 2020-11-19 04:03:42Z

I have come up with a very ugly PHP solution using array_count_values and pushing the data into an array. It works for what I'm trying to do. For what it's worth, I retrieve user agent from Navigator DOM. It usually returns a string similar to this

Mozilla/5.0 (Linux; Android 7.0; SM-G892A Build/NRD90M; wv) AppleWebKit/537.36 (KHTML, like Gecko) Version/4.0 Chrome/67.0.3396.87 Mobile Safari/537.36

From this string alone you can retrieve any information you need (device, browser, etc).

Query

$uniquevisitorsOS= $con->prepare("SELECT userAgent,
COUNT(DISTINCT userAgent) as countVisitor 
FROM geolocation 
WHERE last_update BETWEEN ? AND ? GROUP BY userAgent 
ORDER BY last_update ASC ");
$uniquevisitorsOS->bind_param("ss",$c_datefrom,$c_dateto);
$uniquevisitorsOS->execute();
$visitorOSResult = $uniquevisitorsOS->get_result();

While

//
//
//create arrays of operating systems
$os_arr = Array();
//
//
while ($visitorOS = $visitorOSResult->fetch_assoc()) {
//
//
//
//
//operating systems
/*
Windows
Mac OS X
Android
Linux
Other
*/
//
//
//
//userAgent col
//
$userAgent = $visitorOS['userAgent'];
//
//
//
//find operating system
if (strpos($userAgent, 'Windows') !== false) { 
//
//windows os
//
$os = 'Windows';
//
//
$os_arr[] = $os;
//
//
}else 
if (strpos($userAgent, 'Mac OS X') !== false) {
//mac os
//
$os = 'Mac OS X';
//
//
$os_arr[] = $os;
//
//
}else 
if (strpos($userAgent, 'Android') !== false) {
//android
//
$os = 'Android';
//
//
$os_arr[] = $os;
//
//
}else 
if (strpos($userAgent, 'Linux') !== false) {
//linux
//
$os = 'Linux';
//
//
$os_arr[] = $os;
//
}else{
//Other
//
$os = 'Other';
//
//
$os_arr[] = $os;
//
//
}
//
//
}
//count values
$osArr = (array_count_values($os_arr));
//
//
//test print
print_r($osArr);

Result

Array ( [Linux] => 2 [Mac OS X] => 4 [Android] => 4 [Windows] => 1 )

Collectives™ on Stack Overflow

How to select count data by group by regex in MYSQL?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related