sql - 如何按两列分组以仅显示一行？

Question

下面是我针对 Postgres 数据库运行的查询。

SELECT ad.col1
    ,ad.col2
    ,md.col3
    ,ad.col4
    ,mcd.col5
    ,AVG(mcd.col5 / md.col3) AS dc
    ,AVG(md.col3 / ad.col4) AS cb
FROM tableCount AS md
INNER JOIN tablePop AS ad ON ad.col1 = md.col1
    AND ad.col2 = md.col2
INNER JOIN tableData AS mcd ON mcd.col1 = md.col1
    AND mcd.col2 = md.col2
WHERE md.col2 = 23
    AND md.col1 = '1'
GROUP BY ad.col1
    ,ad.col2
    ,md.col3
    ,ad.col4
    ,mcd.col5
ORDER BY md.col3 DESC limit 30
GROUP BY ad.col1
    ,ad.col2;

下面是我使用上述查询返回到控制台的输出-

col1    col2    col3        col4        col5        dc                                  cb
1       23      48108       224123      479         0.00000000000000000000              0.00000000000000000000
1       23      48108       224123      89          0.00000000000000000000              0.00000000000000000000
1       23      48108       224123      142         0.00000000000000000000              0.00000000000000000000
1       23      48108       224123      1649        0.00000000000000000000              0.00000000000000000000
1       23      48108       224123      14          0.00000000000000000000              0.00000000000000000000
1       23      48108       224123      203         0.00000000000000000000              0.00000000000000000000
1       23      48108       224123      52          0.00000000000000000000              0.00000000000000000000
1       23      48108       224123      62          0.00000000000000000000              0.00000000000000000000
1       23      48108       224123      65          0.00000000000000000000              0.00000000000000000000
1       23      48108       224123      33          0.00000000000000000000              0.00000000000000000000

• 现在，如果您看到我上面的输出，1 和 23 在 col1 和 col2 中多次出现.我想将它们分组在一行中，以便对 col3、col4、col5、dc 和 cb 求和。

所以我希望看到的输出是 -

col1    col2    col3        col4        col5        dc                                  cb
1       23      48108       2241230     278         0.00000000000000000000              0.00000000000000000000

这可以用 SQL 实现吗？

P.S Any Fiddle example would be great.

Answer 1

您想从 group by 中删除 col5:

SELECT ad.col1
    ,ad.col2
    ,md.col3
    ,ad.col4
    ,sum(mcd.col5) as col5
    ,AVG(mcd.col5 / md.col3) AS dc
    ,AVG(md.col3 / ad.col4) AS cb
FROM tableCount AS md
INNER JOIN tablePop AS ad ON ad.col1 = md.col1
    AND ad.col2 = md.col2
INNER JOIN tableData AS mcd ON mcd.col1 = md.col1
    AND mcd.col2 = md.col2
WHERE md.col2 = 23
    AND md.col1 = '1'
GROUP BY ad.col1
    ,ad.col2
    ,md.col3
    ,ad.col4
ORDER BY md.col3 DESC limit 30

我假设最后的 group by 和 order by 是偶然出现的。