CODEWITHSUNDEEP
t-sqlgroup-by
Justin808
Justin808

Reputation: 21522

How can I GROUP BY on a partial field?

Hello I have table in my database holding some browser information I'm 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 overly pretty but its working so far. I'm getting results that look like:

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 to my T-SQL to combine versions with the same major and minor version number, regardless of what the other build numbers are? For example I would like these to all 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

Upvotes: 4

Views: 200

Answers (1)

Ben Hoffstein
Ben Hoffstein

Reputation: 103365

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

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

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

Upvotes: 2

Related Questions