Result of an online MYSQL search, not column

Question

Good morning. I have a table that lists a number of motifs each time they happen. I am using a select to group these reasons and result in the 5 most recurring ones. This is my code:

SELECT count(*) as NumeroDeVezes, motivos FROM relatoriomotivos GROUP BY motivos ORDER BY NumeroDeVezes DESC LIMIT 5

The problem is that I needed the answer to be a row only for the reasons and a row only for the number of times, while I’m getting a column for the reasons and another for the number of times. Is there any way to adjust it?

Answer 1

It is not a complete solution, but it is already a starting point.

_{If I can take a sample of the data in SQL Fiddle, and give more details on the question, I can edit the answer and elaborate better}

If the number of columns is fixed, you can use the IF to calculate:

SELECT
   SUM( IF( motivo='A'), 1, 0 ) AS motivo_a,
   SUM( IF( motivo='B'), 1, 0 ) AS motivo_b,
   SUM( IF( motivo='C'), 1, 0 ) AS motivo_c,
   SUM( IF( motivo='D'), 1, 0 ) AS motivo_d,
   SUM( IF( motivo='E'), 1, 0 ) AS motivo_e
FROM
   motivos

If someone needs the solution for other Dbs that do not have function support IF, can do the same with CASE.

See this post for more details:

Flow control with Mysql

For the titles, unfortunately it would also have to be manual. The UNIONallows generating the two lines, one with title, the other with count:

SELECT 'Motivo A', 'Motivo B', 'Motivo C'
UNION
SELECT
   SUM( IF( motivo='A'), 1, 0 ),
   SUM( IF( motivo='B'), 1, 0 ),
   SUM( IF( motivo='C'), 1, 0 )

Another thing that might interest you is GROUP_CONCAT, that brings several results in a single row, but there would no longer be separate columns. It would be a column with data separated by comma.

Answer 2

You can work the result of your query as a string, in order to enable its manipulation without having to presume its existence in a "hard-coded".

Use the aggregation function GROUP_CONCAT to "merge" into a row each column of your query, using the separator you prefer (here I am using ;):

select 
    GROUP_CONCAT(motivo SEPARATOR ';') as motivos,
    GROUP_CONCAT(numero SEPARATOR ';') as numeros
from (
  select count(*) as numero, motivo from motivos 
  group by motivo order by numero desc limit 5
) tmp;

 ***
|                                      motivos |   numeros |
|----------------------------------------------|-----------|
| motivo 1;motivo 2;motivo 3;motivo 4;motivo 5 | 3;3;3;3;1 |

Then, for each column to return in a row, concatenate them again, using a line break character \n between them. I use the function here CONCAT_WS by allowing to define the general separator at the beginning, allowing the inclusion of more lines in an elegant way, without having to keep repeating the \n:

select 
  CONCAT_WS (
    '\n',
    GROUP_CONCAT(motivo SEPARATOR ';'),
    GROUP_CONCAT(numero SEPARATOR ';')
   ) as resultado
from (
  select count(*) as numero, motivo from motivos 
  group by motivo order by numero desc limit 5
) tmp;

 ***
|                                              resultado |
|--------------------------------------------------------|
| motivo 1;motivo 2;motivo 3;motivo 4;motivo 5
3;3;3;3;1 |

Note that the result is just a column of a record, with line breaking between the group of motifs and the group of numbers.

If you want two records, one for each group, I believe that a UNION solves the problem, with a little redundancy. It would be more elegant if Mysql supported Common Table Expressions, but This will only come in version 8, so follow example with UNION:

select 
    GROUP_CONCAT(motivo SEPARATOR ';') as resultado
from (
  select count(*) as numero, motivo from motivos 
  group by motivo order by numero desc limit 5
) tmp
UNION 
select 
    GROUP_CONCAT(numero SEPARATOR ';') as resultado
from (
  select count(*) as numero, motivo from motivos 
  group by motivo order by numero desc limit 5
) tmp;

 ***
|                                    resultado |
|----------------------------------------------|
| motivo 1;motivo 2;motivo 3;motivo 4;motivo 5 |
|                                    3;3;3;3;1 |

Follow SQL Fiddle containing the above examples: http://sqlfiddle.com/#! 9/644b0b/26

---

That said, only for complementary purposes, the UNION with use of CTE would be like this:

with consulta as (
  select 
      GROUP_CONCAT(motivo SEPARATOR ';') as motivos,
      GROUP_CONCAT(numero SEPARATOR ';') as numeros
  from (
    select count(*) as numero, motivo from motivos 
    group by motivo order by numero desc limit 5
  ) tmp
)
select motivos from consulta 
union
select numeros from consulta;