CODEWITHSUNDEEP
sql
ucfmagic25
ucfmagic25

Reputation: 11

SQL SUM issue. How to get the sum of population?

Table:

cia ( name , region , area , population , gdp )

I'm teaching myself SQL and have come across something I have not been able to figure out. The data that I am trying to pull out of the database is the population total of Belgium, Netherlands, and Luxembourg. I've tried the following:

SELECT SUM(population) FROM cia WHERE name = 'Belgium' AND 'Netherlands' AND 'Luxembourg';

The returned value does not seem correct. What am I missing?

Upvotes: 0

Views: 265

Answers (2)

John Rennpferd
John Rennpferd

Reputation: 121

Uriils answer is the best/most consistently the best option; however, depending on your version of SQL and size of your dataset you may want to consider using UNION to speed up the operation (normally only an issue with Oracle). So if you are/will be in Oracle the following could run faster depending on your setup.

SELECT SUM(result_sum) FROM
(
  SELECT SUM(population) AS result_sum 
    FROM cia 
    WHERE name = 'Belgium' 
  UNION
  SELECT SUM(population) AS result_sum 
    FROM cia 
    WHERE name = 'Netherlands' 
  UNION 
  SELECT SUM(population) AS result_sum 
    FROM cia 
    WHERE name = 'Luxembourg'
)
;

In either case I would suggest using LOWER() and placing your search criteria with all lowercase answers to prevent loosing results due to capitalization inconsistencies.

SELECT SUM(result_sum) FROM
(
  SELECT SUM(population) AS result_sum 
    FROM cia 
    WHERE LOWER(name) = 'belgium' 
  UNION
  SELECT SUM(population) AS result_sum 
    FROM cia 
    WHERE LOWER(name) = 'netherlands' 
  UNION 
  SELECT SUM(population) AS result_sum 
    FROM cia 
    WHERE LOWER(name) = 'luxembourg'
)

Upvotes: 0

juergen d
juergen d

Reputation: 204894

Use or instead of and because a single row can not have these 3 settings at the same time. Your name cell can only be either or.

For a list of values you can also use IN()

SELECT SUM(population)
FROM cia 
WHERE name IN('Belgium', 'Netherlands', 'Luxembourg')

Upvotes: 5

Related Questions