Geek Answers Handbook

How can I GROUP BY on a partial field?

Hello, I have a table in my database containing some information about the browser. I am currently using the following T-SQL to get a summary list:

SELECT Browser, Version, SUM(Count) as Count FROM AnalyticsBrowsers WHERE Month=8 AND Year=2011 AND UserAgent NOT LIKE '%YandexBot%' AND UserAgent NOT LIKE '%Googlebot%' AND UserAgent NOT LIKE '%Yahoo! Slurp%' AND UserAgent NOT LIKE '%Exabot%' AND UserAgent NOT LIKE '%Ezooms%' AND UserAgent NOT LIKE '%facebookexternalhit%' AND UserAgent NOT LIKE '%YPBot%' AND UserAgent NOT LIKE '%Nutraspace%' AND UserAgent NOT LIKE '%AhrefsBot%' AND UserAgent NOT LIKE '%Gigabot%' AND UserAgent NOT LIKE '%GT::WWW%' AND UserAgent NOT LIKE '%WGet%' AND UserAgent NOT LIKE '%cmsworldmap%' AND UserAgent NOT LIKE '%CatchBot%' AND UserAgent NOT LIKE '%SuperPages%' AND UserAgent NOT LIKE '%msnbot%' AND UserAgent NOT LIKE '%nutch%' AND UserAgent NOT LIKE '%Yeti%' AND UserAgent NOT LIKE '%Curl%' AND UserAgent NOT LIKE '%Mediapartners-Google%' AND UserAgent NOT LIKE '%Python-urllib%' AND UserAgent NOT LIKE '%AC-BaiduBot%' AND UserAgent NOT LIKE '%MLBot%' AND UserAgent NOT LIKE '%YahooCacheSystem%' AND UserAgent NOT LIKE '%Xenu%' AND UserAgent NOT LIKE '%DoCoMo%' AND UserAgent NOT LIKE 'Content Crawler' GROUP BY Browser, Version ORDER BY Count DESC

Its not too pretty, but its work is still. I get results that look like this:

 Browser Version Count Mozilla 5.0 804 IE 8.0 738 AppleMAC-Safari 5.0 429 IE 7.0 371 IE 6.0 271 Firefox 6.0 189 IE 9.0 137 Firefox 5.0 68 Firefox 3.6.20 42 Firefox 3.6.10 33 Firefox 4.0.1 20 Unknown 0.0 17 Firefox 3.6.18 10 Firefox 5.0.1 9 Mozilla 1.9.2.20 8 Firefox 3.6.13 6 Opera 9.80 6 Firefox 3.6.3 5 Firefox 3.6.12 4 Opera 9.24 4 IE 5.5 4 Mozilla 1.9.2.16 3 Firefox 3.6.21 3 Firefox 3.6.6 3 Firefox 3.6.8 3 Firefox 4.0 3 Mozilla 4.0 3 Firefox 3.5.19 3

What do I need to do for my T-SQL to combine versions with the same major and minor version number, regardless of what other build numbers are? For example, I would like all of them to be grouped together as Version 3.6

 Firefox 3.6.20 42 Firefox 3.6.10 33 Firefox 3.6.18 10 Firefox 3.6.13 6 Firefox 3.6.3 5 Firefox 3.6.12 4 Firefox 3.6.21 3 Firefox 3.6.6 3 Firefox 3.6.8 3
+4
source share
1 answer

Pretty nasty, but you can replace Version with this expression to get all the text to the second decimal point:

 LEFT(Version, CHARINDEX('.', Version + '.', CHARINDEX('.', Version) + 1) - 1)

This assumes that each version has at least one decimal point (e.g. 1.0).

+2
source

More articles:


All Articles