PostgreSQL Percent

Question:

I can't perform the percentage of this query.

You should take the sum of the "valor_convenio" of each municipality and divide it by the general 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 ";" staying in 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:

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

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

In addition, you already have the query that sums the valor_convenio grouped by municipality (I made some alias changes because the query didn't execute as 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 piece the queries together so that you get the percentage value. For this, a join can be used. It basically serves to join results of complex queries. So adding the join 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 see the following error:

ERROR: column "total.total" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: …start, SUM(agreement_value), SUM(agreement_value)/total.tota…

The problem is that there is the use of a SUM grouping function with 2 different query results, and this needs to be added in a group by clause so that the information can be aggregated by the function. Since the value of the sum total of the valor_convenio column is unique for all municipalities, you can add it in the group by clause without impacting 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 round -rounding function 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. This makes it much easier to solve problems of this type.

Post Views: 0