CODEWITHSUNDEEP
sql
M. Eichhorn
M. Eichhorn

Reputation: 39

How to sum up values when the only connection is count?

SELECT Country.NAME, COUNT(Politics.DEPENDENT) as Number_of__dependent_countries
FROM Country
JOIN Politics ON Politics.DEPENDENT = Country.CODE 
GROUP BY Country.NAME;

I want to sum up the population(Country.Population) of the countries i counted.

Code to create the tables:

CREATE TABLE Politics
(Country VARCHAR(4) CONSTRAINT PoliticsKey PRIMARY KEY,
 Independence DATE,
 WasDependent VARCHAR(40),
 Dependent  VARCHAR(4),
 Government VARCHAR(120));

CREATE TABLE Country
(Name VARCHAR(40) NOT NULL UNIQUE,
 Code VARCHAR(4) CONSTRAINT CountryKey PRIMARY KEY,
 Capital VARCHAR(40),
 Province VARCHAR(40),
 Area INTEGER CONSTRAINT CountryArea
   CHECK (Area >= 0),
 Population INTEGER CONSTRAINT CountryPop
   CHECK (Population >= 0));

Upvotes: 0

Views: 39

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 95072

UPDATE: You want to get the popultion sum of the dependent countries. The dependent countries are not even joined yet. You must join the country table again:

SELECT
  c.name,
  COUNT(*) as number_of_dependent_countries,
  SUM(cd.population) AS population_of_dependent_countries
FROM country c
JOIN politics p ON p.dependent = c.code 
JOIN country cd ON cd.code = p.country
GROUP BY c.name
ORDER BY c.name;

Demo: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=5a1a354955464e427952c8c5811fca49

Original answer:

You want rows from country plus an aggregation, so either join the aggregation result:

SELECT
  c.name, c.population,
  COALESCE(d.countries, 0) AS number_of_dependent_countries
FROM country c
LEFT JOIN
(
  SELECT dependent, COUNT(*) AS countries
  FROM politics
  GROUP BY dependent
) d ON d.dependent = c.code
ORDER BY c.name;

Or use a subquery in the select clause:

SELECT
  c.name, c.population,
  (
    SELECT COUNT(*)
    FROM politics p
    WHERE p.dependent = c.code
  ) AS number_of_dependent_countries
FROM country c
ORDER BY c.name;

Upvotes: 1

Related Questions