您能帮我在SQL中纠正此查询吗

发布于 2025-02-08 05:41:45 字数 400 浏览 1 评论 0原文

SELECT 
    STUDENT_NAME, SUBJECT, TYPE_OF_DISTRIBUTION, 
    SUM(POINTS) * WEIGHT_IN_PERCENTAGE / (COUNT(POINTS) * 100)
FROM 
    assignments a 
JOIN
    distributions d ON a.TYPE_OF_DISTRIBUTION = d.ASSIGNMENT_CATEGORY
GROUP BY
    SUBJECT, TYPE_OF_DISTRIBUTION, STUDENT_NAME 
ORDER BY
    STUDENT_NAME ASC;

第1行的错误：
ORA-00979：不是通过表达式的组

原文

SELECT 
    STUDENT_NAME, SUBJECT, TYPE_OF_DISTRIBUTION, 
    SUM(POINTS) * WEIGHT_IN_PERCENTAGE / (COUNT(POINTS) * 100)
FROM 
    assignments a 
JOIN
    distributions d ON a.TYPE_OF_DISTRIBUTION = d.ASSIGNMENT_CATEGORY
GROUP BY
    SUBJECT, TYPE_OF_DISTRIBUTION, STUDENT_NAME 
ORDER BY
    STUDENT_NAME ASC;

ERROR at line 1:
ORA-00979: not a GROUP BY expression

分享到QQ

分享到微博

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

发布评论

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

小苏打饼 2025-02-15 05:41:45

您正在遇到此错误stroge_in_percentage。如果stright_in_percentage对于组中的每个行都相同，则可以使用（sum（sum（points） * max（wigath_in_percentage）））或您可以sum（points * wove_in_percentage）将其乘以点总和之前：

SELECT 
    STUDENT_NAME, SUBJECT, TYPE_OF_DISTRIBUTION, 
    SUM(POINTS) * max(WEIGHT_IN_PERCENTAGE) / (COUNT(POINTS) * 100)
FROM 
    assignments a 
JOIN
    distributions d ON a.TYPE_OF_DISTRIBUTION = d.ASSIGNMENT_CATEGORY
GROUP BY
    SUBJECT, TYPE_OF_DISTRIBUTION, STUDENT_NAME 
ORDER BY
    STUDENT_NAME ASC;

或

SELECT 
    STUDENT_NAME, SUBJECT, TYPE_OF_DISTRIBUTION, 
    SUM(POINTS * WEIGHT_IN_PERCENTAGE) / (COUNT(POINTS) * 100)
FROM 
    assignments a 
JOIN
    distributions d ON a.TYPE_OF_DISTRIBUTION = d.ASSIGNMENT_CATEGORY
GROUP BY
    SUBJECT, TYPE_OF_DISTRIBUTION, STUDENT_NAME 
ORDER BY
    STUDENT_NAME ASC;

You are getting this error for WEIGHT_IN_PERCENTAGE. If WEIGHT_IN_PERCENTAGE is same for every row in a group you can use (SUM(POINTS) * max(WEIGHT_IN_PERCENTAGE)) or you can SUM(POINTS*WEIGHT_IN_PERCENTAGE) multiply it with POINTS before sum:

SELECT 
    STUDENT_NAME, SUBJECT, TYPE_OF_DISTRIBUTION, 
    SUM(POINTS) * max(WEIGHT_IN_PERCENTAGE) / (COUNT(POINTS) * 100)
FROM 
    assignments a 
JOIN
    distributions d ON a.TYPE_OF_DISTRIBUTION = d.ASSIGNMENT_CATEGORY
GROUP BY
    SUBJECT, TYPE_OF_DISTRIBUTION, STUDENT_NAME 
ORDER BY
    STUDENT_NAME ASC;

SELECT 
    STUDENT_NAME, SUBJECT, TYPE_OF_DISTRIBUTION, 
    SUM(POINTS * WEIGHT_IN_PERCENTAGE) / (COUNT(POINTS) * 100)
FROM 
    assignments a 
JOIN
    distributions d ON a.TYPE_OF_DISTRIBUTION = d.ASSIGNMENT_CATEGORY
GROUP BY
    SUBJECT, TYPE_OF_DISTRIBUTION, STUDENT_NAME 
ORDER BY
    STUDENT_NAME ASC;

回复收藏 0 原文

~没有更多了~