#1111 - 组功能使用无效

发布于 2024-10-13 06:19:54 字数 344 浏览 7 评论 0原文

我正在使用以下查询来尝试获取从每个项目检索最大数量的幻灯片总数（总和），但是我收到以下错误（#1111 - 组功能的无效使用）。这是查询：

SELECT COALESCE(project,'Total') as Project, SUM(MAX(slides)) as Slides 
FROM projects_tbl 
WHERE date BETWEEN '2010-01-01' AND '2010-12-31' 
GROUP BY Project with ROLLUP

如果我删除 SUM()，那么它会起作用，但是，我无法获得所有项目/幻灯片的准确总数。

预先感谢您的所有回复。

原文

I am using the following query in an attempt to get total number(sum) of slides retrieving the max number from each project, however I am receiving the following error (#1111 - Invalid use of group function). Here's the query:

SELECT COALESCE(project,'Total') as Project, SUM(MAX(slides)) as Slides 
FROM projects_tbl 
WHERE date BETWEEN '2010-01-01' AND '2010-12-31' 
GROUP BY Project with ROLLUP

If I remove the SUM(), then the it works, however, I do not get an accurate total for all of the projects/slides.

Thanks in advance for any and all replies.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

骑趴 2024-10-20 06:19:54

SELECT  COALESCE(project,'Total') as Project, SUM(maxslides) AS slides
FROM    (
        SELECT  project, MAX(slides) as maxslides
        FROM    projects_tbl
        WHERE   date BETWEEN '2010-01-01' AND '2010-12-31' 
        GROUP BY
                project
        ) q
GROUP BY
        project WITH ROLLUP

SELECT  COALESCE(project,'Total') as Project, SUM(maxslides) AS slides
FROM    (
        SELECT  project, MAX(slides) as maxslides
        FROM    projects_tbl
        WHERE   date BETWEEN '2010-01-01' AND '2010-12-31' 
        GROUP BY
                project
        ) q
GROUP BY
        project WITH ROLLUP

回复收藏 0 原文

随波逐流 2024-10-20 06:19:54

您可以尝试类似的操作：

SELECT sum(prjmax) 
FROM
(SELECT COALESCE(project,'Total') as Project, MAX(slides) as prjmax
FROM projects_tbl 
WHERE date BETWEEN '2010-01-01' AND '2010-12-31' 
GROUP BY Project with ROLLUP)

您需要获得每个项目的最大值，然后您可以对所有项目求和。

You can try with something like:

SELECT sum(prjmax) 
FROM
(SELECT COALESCE(project,'Total') as Project, MAX(slides) as prjmax
FROM projects_tbl 
WHERE date BETWEEN '2010-01-01' AND '2010-12-31' 
GROUP BY Project with ROLLUP)

You need to obtain the max for each project, and after this you can sum all.

回复收藏 0 原文

~没有更多了~