sql - 子选择的问题

Question

使用 PostgreSQL 9.4；数据库:darwincore2。

我想检索这些字段:

species
county
dataeier
county

我想获取按 county 分组的所有帖子的计数，只显示按总数排序的前 10 个县。我还需要在 species 上选择 distinct，因此如果有 50 个同名的物种，则只将它们计为 1 个物种。并且只计算包含文本“nngg”的 dataeier 的帖子。

这是我失败的代码:

ERROR:  column "darwincore2.species" must appear in the GROUP BY clause or be used in an aggregate function
LINE 5:         SELECT species, county 

SELECT DC2.county, count(DC2.*) AS nav_county 
FROM darwincore2 AS DC2, (
SELECT COUNT(*), county
FROM (
    SELECT species, county 
    FROM darwincore2
    WHERE darwincore2.dataeier ILIKE '%nngg%'
    GROUP BY county
    ) AS temp
  ) AS DC2DIST
WHERE 
DC2.country ILIKE '%norway%' 
    AND 
DC2.county = DC2DIST.county
GROUP BY DC2.county 
ORDER BY nav_county DESC 
LIMIT 10;

更新

我能够通过这段代码得到结果。这似乎比我最初尝试并给我正确的计数要简单得多。

SELECT county, count(DISTINCT species) as nav_county 
FROM darwincore2 
WHERE country ILIKE '%norway%' 
AND dataeier ILIKE '%nngg%' 
GROUP BY county 
ORDER BY nav_county DESC LIMIT 10

给我结果:

Oslo        2254
Bærum       1923
Larvik      1608
Asker       1580
Arendal     1498
Porsgrunn   1405
Nesodden    1317
Bamble      1157
Skien       1123
Fredrikstad 1020

Answer 1

试试这个，将 ' 的拼写错误修复为 '。

SELECT DC2.county, count(DC2.*) AS nav_county 
FROM darwincore2 AS DC2, (
SELECT COUNT(*), county
FROM (
    SELECT species, county 
    FROM darwincore2
    WHERE darwincore2.dataeier ILIKE '%nngg%'
    GROUP BY county
    ) AS temp
  ) AS DC2DIST
WHERE 
DC2.country ILIKE '%norway%' 
    AND 
DC2.county = DC2DIST.county
GROUP BY DC2.county 
ORDER BY nav_county DESC 
LIMIT 10;