CODEWITHSUNDEEP
mysqlsql
Philip Aarseth
Philip Aarseth

Reputation: 311

How to sum top results?

I'm wondering how one would sum the results from a query?

I want to know how many people live in total in the three biggest cities in Norway. I'm using mysql, the world.sql sample database in mysql workbench.

This is the closest I've gotten

SELECT population
FROM city 
WHERE CountryCode = 'NOR' 
ORDER BY population DESC 
LIMIT 3

There's a few problems here namely this gives me three results instead of one, and while using LIMIT which actually limits how many results it gives, not how many it uses.

Any ideas?

Upvotes: 1

Views: 89

Answers (4)

Santhosh
Santhosh

Reputation: 729

Read on subqueries. Make your current query a subquery and get sum from your subquery.

SELECT SUM(population) FROM (
SELECT population
FROM city 
WHERE CountryCode = 'NOR' 
ORDER BY population DESC 
LIMIT 3) p

You query will now act as a virtual table, from which you can you can write a select query to get the sum

Upvotes: 0

Priyanshu
Priyanshu

Reputation: 881

select sum(population) from (SELECT population FROM city WHERE CountryCode = 'NOR' ORDER BY population DESC LIMIT 3) temp

Upvotes: 0

Jens
Jens

Reputation: 69440

simply sum the result:

select  sum(population) from (SELECT population
FROM city 
WHERE CountryCode = 'NOR' 
ORDER BY population DESC 
LIMIT 3) t1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You would use a subquery:

SELECT SUM(population)
FROM (SELECT population
      FROM city 
      WHERE CountryCode = 'NOR' 
      ORDER BY population DESC 
      LIMIT 3
     ) cp

Upvotes: 3

Related Questions