获取“[错误] 1111 - 无效使用组功能的”错误
问题描述:
目前我在做这个查询:获取“[错误] 1111 - 无效使用组功能的”错误
SELECT Date_Format(M.Signupdate,"%Y-%m") as YearMonth,
SUM(IF(Count(H.Nr) >= 0,1,0)) ,
SUM(IF(Count(H.Nr) >= 1,1,0)) ,
SUM(IF(Count(H.Nr) >= 2,1,0)) ,
SUM(IF(Count(H.Nr) >= 3,1,0))
FROM people M
INNER JOIN History H ON H.login = M.Login
WHERE
M.Masteraccount = "" AND
M.logincount > 5 AND
DATEDIFF(M.lastlogin,M.Signupdate) >= 3 AND
DATEDIFF(H.EntryDate,M.Signupdate) <= 151
GROUP BY YearMonth ORDER BY YearMonth ;
不过,我不断收到“[错误] 1111 - 无效使用组功能“错误。如果我删除SUM的它工作得很好。有人请给我答案吗?
答
你不能在GROUP BY
使用别名。您需要将Date_Format(M.Signupdate,"%Y-%m")
放入GROUP BY
才能使其正常工作。你也不能嵌套聚合函数,所以你需要使用一个子查询来获取count
,然后使用sum
:
select YearMonth,
SUM(IF(CountNR >= 0,1,0)) ,
SUM(IF(CountNR >= 1,1,0)) ,
SUM(IF(CountNR >= 2,1,0)) ,
SUM(IF(CountNR >= 3,1,0))
from
(
SELECT Date_Format(M.Signupdate,"%Y-%m") as YearMonth,
Count(H.Nr) CountNR
FROM people M
INNER JOIN History H
ON H.login = M.Login
WHERE M.Masteraccount = "" AND
M.logincount > 5 AND
DATEDIFF(M.lastlogin,M.Signupdate) >= 3 AND
DATEDIFF(H.EntryDate,M.Signupdate) <= 151
GROUP BY Date_Format(M.Signupdate,"%Y-%m")
) src
GROUP BY YearMonth
ORDER BY YearMonth;
感谢您的快速反应!然而,这仍然查询递给我回同样的错误:( –
@VincentJames看到我的编辑,我错过了嵌套聚集函数。再次感谢 – Taryn
,但“DATEDIFF(H.EntryDate,M.Signupdate)