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?