无法获得正确的结果sql plus(Unable to get the correct result sql plus)

无法获得正确的结果sql plus(Unable to get the correct result sql plus)

结果显示，我无法得到正确的结果。 我想整理成本列，但失败了。 总费用应该是成本的总和但是当我分组时它变成6000+。 我做的任何错误？

Select p.title, SUM(c.cost) as Total_Fees, c.cost from programmes p Inner Join courses c ON p.programme_id = c.programme_id Inner join class_schedules cs ON c.course_id = cs.course_id AND c.semester = 4 Inner join class_enrollments ce ON cs.schedule_id = ce.schedule_id Inner Join students s ON ce.student_id = s.student_id and s.student_id = '13WAR1001' GROUP BY p.title, c.cost; TITLE TOTAL_FEES COST --------------------------------------------------------------------------------- ---------- ---------- Bachelor of Information Technology (Honours) in Internet Technology 1728 576 Bachelor of Information Technology (Honours) in Internet Technology 1353 451 Bachelor of Information Technology (Honours) in Internet Technology 1184 592 Bachelor of Information Technology (Honours) in Internet Technology 1800 600 TITLE COURSE_ID TOTAL_FEES ---------------------------------------------------------------------------------------------------- ---------- ---------- Bachelor of Information Technology (Honours) in Internet Technology 14 1728 Bachelor of Information Technology (Honours) in Internet Technology 15 1353 Bachelor of Information Technology (Honours) in Internet Technology 13 1800 Bachelor of Information Technology (Honours) in Internet Technology 12 1184 SQL> SELECT SUM(cost) from courses where course_id = 14 or course_id = 15 or course_id = 13 or course_id = 12; SUM(COST) ---------- 2219

////删除c.cost和c.cost之后的组

TITLE TOTAL_FEES ---------------------------------------------------------------------------------------------------- ---------- Bachelor of Information Technology (Honours) in Internet Technology 6065

I can't get the correct result, as the result shown. I want to SUM up the cost column but failed. The total fees should be sum of cost but when I group by it become 6000+. Any mistake I did?

Select p.title, SUM(c.cost) as Total_Fees, c.cost from programmes p Inner Join courses c ON p.programme_id = c.programme_id Inner join class_schedules cs ON c.course_id = cs.course_id AND c.semester = 4 Inner join class_enrollments ce ON cs.schedule_id = ce.schedule_id Inner Join students s ON ce.student_id = s.student_id and s.student_id = '13WAR1001' GROUP BY p.title, c.cost; TITLE TOTAL_FEES COST --------------------------------------------------------------------------------- ---------- ---------- Bachelor of Information Technology (Honours) in Internet Technology 1728 576 Bachelor of Information Technology (Honours) in Internet Technology 1353 451 Bachelor of Information Technology (Honours) in Internet Technology 1184 592 Bachelor of Information Technology (Honours) in Internet Technology 1800 600 TITLE COURSE_ID TOTAL_FEES ---------------------------------------------------------------------------------------------------- ---------- ---------- Bachelor of Information Technology (Honours) in Internet Technology 14 1728 Bachelor of Information Technology (Honours) in Internet Technology 15 1353 Bachelor of Information Technology (Honours) in Internet Technology 13 1800 Bachelor of Information Technology (Honours) in Internet Technology 12 1184 SQL> SELECT SUM(cost) from courses where course_id = 14 or course_id = 15 or course_id = 13 or course_id = 12; SUM(COST) ---------- 2219

//// after remove the c.cost and group by c.cost

TITLE TOTAL_FEES ---------------------------------------------------------------------------------------------------- ---------- Bachelor of Information Technology (Honours) in Internet Technology 6065

最满意答案

您不应在选择或组中包含cost ，因为您不希望根据标题和成本计算组的总和。 改为：

SELECT p.title, SUM(c.cost) AS Total_Fees FROM programmes p INNER JOIN courses c ON p.programme_id = c.programme_id INNER JOIN class_schedules cs ON c.course_id = cs.course_id AND c.semester = 4 INNER JOIN class_enrollments ce ON cs.schedule_id = ce.schedule_id INNER JOIN students s ON ce.student_id = s.student_id AND s.student_id = '13WAR1001' GROUP BY p.title;

You shouldn't include costin the select or the group by as you do not want to compute the sum over groups based on title and cost. Do this instead:

SELECT p.title, SUM(c.cost) AS Total_Fees FROM programmes p INNER JOIN courses c ON p.programme_id = c.programme_id INNER JOIN class_schedules cs ON c.course_id = cs.course_id AND c.semester = 4 INNER JOIN class_enrollments ce ON cs.schedule_id = ce.schedule_id INNER JOIN students s ON ce.student_id = s.student_id AND s.student_id = '13WAR1001' GROUP BY p.title;