Order by datetime and extracting substring

user_11860534 0
Viewed 40

I have a table similar to this:

|datetime            | request                                  | result |
|2022-05-24 15:23:56 | request GET http://yahoo.com/api/asidasd | 200    |
|2022-05-24 16:20:01 | request POST http://google.com/api/125   | 401    |
|2022-05-23 10:00:02 | request POST http://google.com/api/255   | 500    |
|2022-05-23 10:00:00 | request POST http://google.com/api/255   | 200    |
|2022-05-23 09:59:00 | request POST http://google.com/api/255   | 200    |
|2022-05-23 01:23:56 | request GET http://yahoo.com/api/1516    | 200    |
|2022-05-23 01:22:50 | request GET http://yahoo.com/api/as45    | 200    |

it's not ideal for a table, but I still need to use it like that.

I need a query that filters and groups by datatable, by API and by status result, like that:

|datetime   | request    | result | times |
|2022-05-24 | yahoo.com  | 200    | 1     |
|2022-05-24 | google.com | 401    | 1     |
|2022-05-23 | google.com | 500    | 1     |
|2022-05-23 | google.com | 200    | 2     |
|2022-05-23 | yahoo.com  | 200    | 2     |

Issue1: I'm not sure how to filter datetime data to only consider year-month-day and group them

Issue2: I don't know how to filter the data from the "request" column to get the request link, as it is a cell with line breaks (I would have to get the last word of the first line and then filter to the .com)

Issue3: this error: In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'response'; this is incompatible with sql_mode=only_full_group_by when a try this select:

select request, count(result) from request_log where created_at >'2022-02-01' order by request
mysql
1 Answers

The version independent query would be

SELECT t.datetime, t.request, t.result, COUNT(*) AS times 
FROM ( 
    SELECT 
        DATE(datetime) AS datetime, 
        SUBSTRING_INDEX(SUBSTRING_INDEX(request, "//", -1), "/", 1) AS request, 
        result 
    FROM request_log
) t 
GROUP BY t.datetime, t.request, t.result 
ORDER BY request, datetime

Fiddle https://www.db-fiddle.com/f/qFWjmcDF8nagojPQxTdg2H/0

user_58768030
Related