当唯一的连接是count时，如何求和值？

qnzebej0 于 2021-08-09 发布在 Java

关注(0)|答案(1)|浏览(288)

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

我想把我统计过的国家的人口（country.population）加起来。
创建表的代码：

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));

sql

来源：https://stackoverflow.com/questions/62145351/how-to-sum-up-values-when-the-only-connection-is-count

1条答案

按热度按时间

hsgswve41#

更新：你想得到附属国家的人口总数。附属国甚至还没有加入。您必须再次加入country表：

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;

演示：https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=5a1a354955464e427952c8c5811fca49
原始答案：
您需要来自country的行加上聚合，因此可以加入聚合结果：

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;

或在select子句中使用子查询：

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;

赞(0）回复(0）举报 2021-08-09

我来回答

当唯一的连接是count时，如何求和值？

1条答案

相关问题

热门标签

最新问答