无法解决此问题:“无法按汇总分组" [英] Can't fix this: "Cannot group by an aggregate"

问题描述

很抱歉这个愚蠢的问题.我已经读过很多关于同一问题的主题，但是仍然无法解决此问题...

Sorry for the silly question. I have read a lot of threads about the same issue, but still, can't fix this...

SELECT   company_name, SUM(clicks)
FROM     table1
WHERE    code = 'ES'
GROUP BY 1
ORDER BY clicks DESC
LIMIT 100;

结果是:

GROUP BY列表中不存在表达式"clicks"

Expression 'clicks' is not present in the GROUP BY list

如果我尝试这样做:

SELECT   company_name, SUM(clicks)
FROM     table1
WHERE    code = 'ES'
GROUP BY 1,2
ORDER BY clicks DESC
LIMIT 100;

这就是我得到的:

无法按汇总分组.

Cannot group by an aggregate.

如果我尝试不对点击次数"进行汇总:

If I try with no aggregation on "clicks":

SELECT   company_name, clicks
FROM     table1
WHERE    code = 'ES'
GROUP BY 1
ORDER BY clicks DESC
LIMIT 100;

错误:GROUP BY列表中不存在表达式"clicks"

Error: Expression 'clicks' is not present in the GROUP BY list

如果添加点击的方式是:

And if add clicks to the group by:

SELECT   company_name, clicks
FROM     table1
WHERE    code = 'ES'
GROUP BY 1,2
ORDER BY clicks DESC
LIMIT 100;

结果不是我所需要的:

Company_name | clicks
-------------+--------
    company1 | 250   
    company1 | 340
    company2 | 100 
    company2 | 300
    company2 | 344

我如何获得?:

Company_name | clicks
-------------+-------
    company1 | 590
    company2 | 744

谢谢！

推荐答案

您应该这样做

SELECT company_name, SUM(clicks) as clicks
FROM table1   
WHERE code = 'ES'  
GROUP BY company_name 
ORDER BY clicks DESC 
LIMIT 100;

您的第一个查询是正确的，不确定您为什么收到此错误.但是，您的第二个查询不正确，因为您无法按聚合汇总的第二列进行分组.

Your first query is correct, not sure why are you getting the error. Your second query is however incorrect as you cannot group by second column, which you have derived by aggregation.

使用 group by 子句中的数字，虽然看起来更整洁" ，但实际上增加了混乱.因此，尝试完全省略它们.使用适当的列别名，并在 group by 和 order by 中使用以避免混淆.

Using numbers in group by clause, while looks neater, it actually adds to the confusion. Hence try to omit them completely. Use proper column alises and use them in group by and order by to avoid confusion.

这篇关于无法解决此问题:“无法按汇总分组"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！