Tuples separated by commas

3

I'm doing a query in the database (mysql) where I have a code and through this code there are several types of people.

I need when I make query come like this

ID | NOMES
1  | Fulano 1, Fulano 2, Fulano 3
2  | Fulano 2, Fulano 4
3  | Fulano 5

Today I make the following query:

select DESP.codTipoDespesa as codigo, DESP.descTipoDespesa as descricao, VALOR.valor as valor, DEPT.nome as nome
FROM
    (
    select codTipoDespesa, descTipoDespesa from despesas
    ) DESP
  LEFT JOIN
    (
      select codTipoDespesa, sum(valorDespesa) as valor FROM despesas GROUP BY codTipoDespesa
    ) VALOR
  ON DESP.codTipoDespesa = VALOR.codTipoDespesa
  LEFT JOIN
    (
      select idPessoa, valorDespesa, codTipoDespesa from despesas ORDER BY valorDespesa DESC
    ) CDEP
  ON DESP.codTipoDespesa = CDEP.codTipoDespesa
  LEFT JOIN
   (
       select id, nome from pessoa
   )  DEPT
ON DEPT.id = CDEP.idPessoa
GROUP BY DESP.codTipoDespesa
ORDER BY valor desc

So I need the result to look like this:

codigo | descricao | valor | nome
32     | Lapiseira | 3000  | Robertino Algusto, Felipe Abril, Ronaldo, Pedro,Gabriel
34     | Borracha  | 200   | Felipe Abril,Cintia , Martilucia, Joaozinho, Amanarque

Structure of tables:

PESSOAS
-------------------------------
numero         | AUTOINCREMENT
id             | text
nome           | text
partido        | text
tagLocalizacao | text
--------------------------------
DESPESAS
--------------------------------
numero          | AUTOINCREMENTO
idPessoa        | text
codTipoDespesas | text
mesDespesa      | text
descTipoDespesa | text
valorDespesa    | text
---------------------------------
  

I need to show the total spending by category in year 2015 sorted from highest to lowest; [value added every month] - This I'm already doing in my query.   There for every category of this need to catch the people who spent the most

     

Expending Tuple Expendables

     

number | idP People | About Us | month | description | value

     

1 | 12193 | 34 | 2 | Pencil | 6485

In addition I have to show the id of the first 5.

I found this document here , is exactly what I want but I'm not sure how to do it, can anyone help me?

  

With the query I have used it returns me only the last name, I need to return the last 5 and separated by commas.   How to do this straight from sql?

    
asked by anonymous 09.08.2016 / 20:46

1 answer

2

In MySQL you can use the GROUP_CONCAT function to do this, in your case it would look like this

GROUP_CONCAT(DEPT.nome SEPARATOR ', ') as nome

This function is to group your string to a desired delimiter when you use some grouping.

You can read more about it here link

    
30.09.2016 / 16:50