there is a table

zapros | result | time | code 1 | ok | 14.12 | 200 1 | err | 14.12 | 403 1 | err | 14.12 | 404 2 | ok | 14.12 | 200 3 | ok | 14.12 | 200 3 | ok | 14.12 | 200 4 | err | 14.12 | 404

Help make a request. It is necessary to group by field zapros, count for each grouped zapros the number of errors (result = err) and their total number. For each grouped zapros, you need to further group by code and also calculate how many and which codes are present in the table. Calculate the percentage of successful requests by the formula 1-err / all for each grouped request, and select all all the grouped requests with a coeff> 0.5 at the output should work

 zapros | err | all | 200 | 403 | 404 | rate 1 | 2 | 3 | 1 | 1 | 1 | 0.34 2 | 0 | 1 | 1 | 0 | 0 | 1 ....
  • The number and value of codes hopefully fixed. because dynamically creating columns won't work - Mike
  • No, the number and meaning of the codes is not known in advance - user193361
  • one
    It's impossible. The SQL language does not allow to dynamically change the number of columns in the result. Each column should be described in the query - Mike
  • As an option - to write a wild stored procedure, which first looks at what codes are found at all, then on the basis of this data creates the text of the sql query and executes it - Mike
  • I would recommend using any report builder. They are sharpened just for that. - Akina

1 answer 1

 select zapros, sum(result='err') as err, count(1) as all, sum(code=200) as code200, sum(code=403) as code403 from table group by zapros

Unfortunately, the only way is that all columns of codes should be described in the request itself. If you need to dynamically - then form a separate line for each code and deploy them to the horizontal already on the client.

  • Well, or use the prepared statement, getting the list of fields from INFORMATION_SCHEMA.COLUMNS. - Akina
  • @Akina And what will help INFORMATION_SCHEMA.COLUMNS there are existing columns in the existing tables - Mike
  • one
    And, yes, sorry ... well, then it is even simpler to receive a list of codes with a regular request, and build the request text using it. Although this does not remove the problems of a non-deterministic result structure ... - Akina
  • I forgot one moment, made an amendment to the question of the coefficient, tell me how to enter the condition correctly? - user193361
  • @ user193361 Well, write 1 - sum(result='err') / count(1) and write the same formula in filter - having - Mike