CODEWITHSUNDEEP
mysql
Foivos Allayiotis
Foivos Allayiotis

Reputation: 109

MySQL - Group by similar rows (not the same exactly)

I have a table like the following

enter image description here

Is there any way I can group rows that have a similar content_type, for example, sort all the rows that start with 'application' or 'image'. Maybe with a regex, I haven't been able to figure it out

The query is the following:

SELECT 
    ROUND(SUM(timings_wait), 2) AS total
    , res_h_value AS content_type
    , DAYOFWEEK(startedDateTime) AS d
    , HOUR(DATE_FORMAT(startedDateTime
    ,'%Y-%c-%d %T')) AS h     
FROM req_res_head    
INNER JOIN entries ON req_res_head.entry_id = entries.id    
WHERE res_h_name = 'content-type'    
GROUP BY content_type

ORDER BY d;

EDIT: Sorry for the image, it's just to show a sample of the table. Also I am using SQL version 5.7.26

Thanks in advance

Upvotes: 0

Views: 174

Answers (2)

Tomas Creemers
Tomas Creemers

Reputation: 2715

In addition to @Marco's answer, you can create a completely arbitrary grouping using the CASE operator:

SELECT
  (
    CASE
      WHEN LOCATE('/', content_type, LOCATE('/', content_type) + 1) <> 0 THEN 'invalid' -- more than 1 slash in content_type
      WHEN SUBSTRING(content_type FROM 1 FOR 9) = 'image/png' THEN 'PNG image'
      WHEN SUBSTRING(content_type FROM 1 FOR 6) = 'image/' THEN 'other image'
      WHEN SUBSTRING(content_type FROM 1 FOR 12) = 'application/' THEN 'application'
      ELSE 'unknown'
    END
  ) AS classifier,
  COUNT(0)
FROM
  partresult
GROUP BY
  classifier

See this fiddle for an example.

Upvotes: 1

Marco
Marco

Reputation: 587

If you add the version of the database we could be more accurate for the moment i can answer with this:

SELECT 
    ROUND(SUM(timings_wait), 2) AS total, 
    SUBSTRING_INDEX(res_h_value, '/', 1) AS content_type,
    DAYOFWEEK(startedDateTime) AS d,
    HOUR(DATE_FORMAT(startedDateTime,'%Y-%c-%d %T')) AS h     
FROM req_res_head    
INNER JOIN entries ON req_res_head.entry_id = entries.id    
WHERE res_h_name = 'content-type'    
GROUP BY content_type
ORDER BY d;

Upvotes: 2

Related Questions