SELECT grp, SUM(cnt) AS cnt FROM (
SELECT url_request,
CASE
WHEN url_request like ‘%metadata%’ THEN ‘metadata’
WHEN url_request like ‘%map%’ THEN ‘map’
WHEN url_request like ‘%graph%’ THEN ‘graph’
WHEN url_request like ‘%values%’ OR url_request like ‘%table%’ THEN
‘values’
WHEN url_request like
‘%download%’ THEN ‘download’
ELSE
‘other’
END
AS grp, SUM(number) AS cnt
FROM
hits_per_urlrequest
GROUP BY
hits_per_urlrequest.url_request)
AS foo
GROUP BY grp
ORDER BY cnt DESC