Tuples separated by commas

Asked

Viewed 283 times

3

I’m making a query in the database (mysql) where I have a code and by this code there are several types of people.

I need when you do the query come like this

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

Today I do 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

Then I need the result to be 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

Table structure:

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 list of total expenditure by category in the year 2015 ordered from highest to lowest; [value expenses added each month] - This I am already doing in my query. Alas for each category of this need to catch the people who spent the most

Ex of tuples of scales of expenses

numero | idPessoa | codDespesas | month | Description | value

1 | 12193 | 34 | 2 | Lapiz | 6485

Besides this I have to show the id of the first 5.

I found this document here, it’s exactly what I want but I’m not sure how to do it, someone can help me ?

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

sql

  • In which database?

  • 1

    I am doing in sqlite, but the query is mysql

  • I will increment the question to demonstrate how do I better understand

  • @rray I updated my question

  • @rray Could you help me based on the document I put ? I am not knowing how to create solution

  • Amigo puts his bank structure in your question, I’m thinking that to greatly simplify the query you are using, I just don’t understand the bank structure yet.

  • @Euler01 I updated

  • @Euler01 any opinion on ?

Show 3 more comments

1 answer

2


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

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

This function serves to group your string by a desired delimiter, when you use some grouping.

You can read more about it at this link http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.