Percentage Postgresql

Question

I cannot perform the percentage of this query .

You should take the sum of "valor_convenio" of each municipality and divided by the total sum of the "valor_convenio" :

<h3>Entrada SQL SEM CALCULO DE PORCENTAGEM</h3>
SELECT
    nome_municipio,
        SUM(valor_convenio)<br/>
FROM
    paraiba.vigente<br/>
WHERE 
    convenente LIKE '%(MUNICIPAL)%'<br/>
GROUP BY
    nome_municipio<br/>
ORDER BY
    "Total Conveniado (R$)" DESC;<br/>


<h3>Exemplo de Saída ATUAL:</h3>------------------------------------------------------
<br/>|JOAO PESSOA.......|R$272.789.654,75|
<br/>|CAMPINA GRANDE|R$182.080.728,84|
<br/>|PIANCO...................|..R$35.392.580,61|
<br/>|SUME......................|..R$34.040.127,05|
<br/>|CABEDELO.............|..R$30.652.583,47|
<br/>|SOUSA....................|..R$22.075.733,70|
<br/>|PATOS.....................|..R$20.061.310,59|
<br/>-------------------------------------------------------

<h3>Exemplo de Saída QUE PRECISO:</h3>--------------------------------------------------------------
<br/>|JOAO PESSOA.......|R$272.789.654,75|..22%
<br/>|CAMPINA GRANDE|R$182.080.728,84|..15%
<br/>|PIANCO...................|..R$35.392.580,61|....3%
<br/>|SUME......................|..R$34.040.127,05|....3%
<br/>|CABEDELO.............|..R$30.652.583,47|....2%
<br/>|SOUSA....................|..R$22.075.733,70|....2%
<br/>|PATOS.....................|..R$20.061.310,59|....1%
<br/>--------------------------------------------------------------

---

At the end of I export to csv file separated by ";" by this pattern:

JOAO PESSOA;272.789.655;22%<br/>
CAMPINA GRANDE;182.080.729;15%<br/>
PIANCO;35.392.581;3%<br/>
SUME;34.040.127;3%<br/>
CABEDELO;30.652.583;2%<br/>
SOUSA;22.075.734;2%<br/>
PATOS;20.061.311;2%<br/>

Answer 1

First, you need a query that returns the sum of all values in the column valor_convenio. The query would then be simple:

select
    sum (valor_convenio) as total
from paraiba.vigente
WHERE
    convenente LIKE '%(MUNICIPAL)%'

Also, you already have the query that adds the valor_convenio grouped by municipality (I made some alias modifications because the query did not execute the way you wrote it):

SELECT
    nome_municipio,
    SUM(valor_convenio) as total
FROM paraiba.vigente
WHERE
    convenente LIKE '%(MUNICIPAL)%'
GROUP BY nome_municipio
ORDER BY total DESC;

What is needed now is just to join the queries so that you get the percentage value. For this, you can use a join. It basically serves to unite complex query results. So adding the merge would look something like:

SELECT
    nome_municipio,
    SUM(valor_convenio),
    SUM(valor_convenio) / total.total AS total
FROM paraiba.vigente, (
    select sum (valor_convenio) as total 
    from paraiba.vigente 
    WHERE convenente LIKE '%(MUNICIPAL)%'
) total 
WHERE convenente LIKE '%(MUNICIPAL)%' 
GROUP BY nome_municipio 
ORDER BY total DESC;

If you run the above query, you will come across the following error:

ERROR: column "total.total" must appear in the GROUP BY clause or be used in an Aggregate Function LINE 1: ...nicipio, SUM(valor_convenio), SUM(valor_convenio)/total....

The problem is that there is the use of a grouping function SUM with 2 different queries, and this needs to be added in a clause group by so that the information can be aggregated by the function. As the value of the total sum of the column valor_convenio is unique for all municipalities, you can add it in the clasp group by without impact on the final result. So you would have something like:

SELECT 
    nome_municipio, 
    SUM(valor_convenio),
    SUM(valor_convenio) / total.total AS total 
FROM paraiba.vigente, (
    select sum (valor_convenio) as total 
    from paraiba.vigente
    WHERE convenente LIKE '%(MUNICIPAL)%'
) total 
WHERE convenente LIKE '%(MUNICIPAL)%'
GROUP BY nome_municipio, total.total 
ORDER BY total DESC;

To adjust the query by adding a percentage format, you can do something like the example below, using the rounding function round and the string concatenation operator ||:

SELECT 
    nome_municipio,
    SUM(valor_convenio),
    round((SUM(valor_convenio)/total.total)*100, 2) || '%' AS total
FROM paraiba.vigente, (
    select sum (valor_convenio) as total  
    from paraiba.vigente 
    WHERE convenente LIKE '%(MUNICIPAL)%'
) total
WHERE convenente LIKE '%(MUNICIPAL)%'
GROUP BY nome_municipio, total.total
ORDER BY total DESC;

Tip: Whenever you have complex queries to build, build each piece separately and then think about the logic of putting them together. So it’s much easier to solve problems like this.