I'm new to sql and I'm having trouble performing the sum total of values in a count , 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 have the sum of airports per country also have the total sum of airports, I tried to use SUM(count) but gave the error:
ERROR: aggregate function calls can not be nested.
Try 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 do not think it's the right way because I think I'm going to run the count multiple times unnecessarily, depending on what your actual need certainly has a better way to do it.
As you were doing a join and then a where where the join column was null, I removed it because it was not correct.
The part of picking up all the airports that are not in the station table, 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;
As a result, I put the total number of airports without stations there.
You can use UNION to join 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 in sqlfiddle .
SELECT country, total, sum (total) over () From (select A.Pais, count (*) as total
from Airports A left join Stations E
on A.Sigla = E.ICAO where E.ICAO IS NULL group by A.Pais order by Country) t;
Here we have the use of Windows functions. Using the sum function with the over clause without partitioning. It has something like this ... Country at 10.10 Country b 2. 12 Country c. 25. 37
See that the total will appear on the last line. If you want the total to appear on all the knife lines ...
SELECT country, total, sum (total) over (partition by particao) From (select A.Pais, count (*) the total , 1 aspartame from Airports A left join Stations E
on A.Sigla = E.ICAO where E.ICAO IS NULL group by A.Pais order by Country) t;