Sum the values of a Count(*) in sql

Asked

Viewed 2,562 times

1

I’m new in sql and I’m having a hard time making the sum total of the values of a Count, follows below the code:

SELECT  A.Pais, count(*)
from Aeroportos A
left join Estacoes E
on A.Sigla = E.ICAO
where E.ICAO IS NULL
group by A.Pais
order by Pais;

I would like to not only have the sum of airports per country also have the total sum of airports, I tried to use the SUM(count) but made the mistake:

ERROR: Aggregate Function calls cannot be nested.

sql postgresql

  • Everything in the same query will not work.

  • could put a sketch of the tables?

  • the total sum of airports is basically select count(*) from Aeroportos

  • The count is only of Aeroportos that do not have Estações related ?

  • Simm, only from airports that don’t have stations

3 answers

2

You can use UNION to unite the count of Aeroportos in each country with the total amount of Aeroportos in the same query (without any subselect):

(SELECT a.Pais AS Pais, count(1) FROM Aeroportos a
LEFT JOIN Estacoes e ON (a.Sigla = e.ICAO)
WHERE e.ICAO IS NULL GROUP BY a.Pais)
UNION
(SELECT 'TOTAL' AS Pais, count(1) FROM Aeroportos a
LEFT JOIN Estacoes e ON (a.Sigla = e.ICAO)
WHERE e.ICAO IS NULL)
ORDER BY Pais

See working on sqlfiddle.

  • I know it’s not the same format as the query (separated into columns), but I particularly find it more elegant this way (separated into rows)

  • I’d still say it’s possible to use UNION ALL, since there is no chance of repeated tuples in both queries

1


Try it like this:

SELECT  
    A.Pais, 
    count(*) as qtd_pais,
    (select count(*) from Aeroportos) as total
from Aeroportos A
group by A.Pais
order by Pais;

I believe it is not the right way because I think it will run Count several times unnecessarily, depending on what your real need certainly has better way to do it.

as you were making a Join and then a Where where the Join column was null, I withdrew because it was not correct.

The part of picking up all airports that are not in the table of stations, can be done like this:

   SELECT  
        A.Pais, 
        count(*) as qtd_pais,
        (select count(*) from Aeroportos x where not exists (select 1 from Estacoes ex where ex.icao = x.sigla)  ) as total_sem_estacoes,
        (select count(*) from Aeroportos) as total
    from Aeroportos A
    WHERE NOT EXISTS (select 1 from Estacoes e where e.icao = a.sigla)
    group by A.Pais
    order by Pais;

Of break I put there the total quantity of airports without stations.

  • I will test here ! thank you, the Join that made is to pick up only the airports that are in the airport table and have no match in the table stations . sorry I missed explaining this part of the code

  • @Linkonlouvison added how to do the part of not having matches in the table stations.

  • 1

    Thank you @Rovann Linhalis exactly what I wanted to do

-1

SELECT country, total, sum(total) over () From (select A.Parents, Count(*) as total

from Airports A left Join Estacoes E

on A.Acronym = E.ICAO Where E.ICAO IS NULL group by A.Pais order by Pais) t;

Here we have the use of Windows functions. Using the sum function with the over clause without partitioning. There will be something like that as a result... Country to 10. 10 Country b 2. 12 Country c. 25. 37

Note that the total will appear on the last line. If you want the total to appear in all knife lines...

SELECT country, total, sum(total) over (Partition by partition) From (select A.Parents, Count(*) as total , 1 as a particao from Airports A left Join Estacoes E

on A.Acronym = E.ICAO Where E.ICAO IS NULL group by A.Pais order by Pais) t;

  • 1

    Carlos, whenever you answer a question try to leave an explanation with the code.

Browser other questions tagged sql postgresql

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