我在sql中的sum函数有问题,如下所示:
SELECT Member.Name, Book.Title AS BookBought, Order_Member_Det.Quantity, Order_Member_Det.Price, Order_Member.Total
FROM Order_Member
INNER JOIN OrderWithMember ON Order_Member.OrderID = OrderWithMember.OrderID
INNER JOIN Order_Member_Det ON OrderWithMember.MemberOrderID = Order_Member_Det.MemberOrderID
INNER JOIN Member ON OrderWithMember.MemberID = Member.MemberID
INNER JOIN Book ON Order_Member_Det.BookID = Book.BookID
结果是:
Name BookBought Quantity Price Total
----------------------------------------------------
John Flip it 2 50 150
John California Penal Code 1 50 150
Jack Forum Internationale 2 80 160
Alice Securitization 2 80 210
Alice The Cervical Spine 1 50 210
Kevin Trading Commodities 1 55 55
James Aerodynamics 1 90 90
我想通过使用SUM(TOTAL)在表中添加所有总数的SUM:
SELECT Member.Name, Book.Title AS BookBought, Order_Member_Det.Quantity, Order_Member_Det.Price, Order_Member.Total, **SUM(Order_Member.Total)**
FROM Order_Member
INNER JOIN OrderWithMember ON Order_Member.OrderID = OrderWithMember.OrderID
INNER JOIN Order_Member_Det ON OrderWithMember.MemberOrderID = Order_Member_Det.MemberOrderID
INNER JOIN Member ON OrderWithMember.MemberID = Member.MemberID
INNER JOIN Book ON Order_Member_Det.BookID = Book.BookID
但后来我收到一个错误:
消息8120,级别16,状态1,行264列“Member.Name”在选择列表中无效,因为它不包含在聚合函数或GROUP BY子句中 .
但是如果我用这样的总和创建新的选择它可以工作:
SELECT SUM(Total) From Order_Member;
它可以产生这个输出:
ColumnName
665
那么如何在给定的两个select中组合,我也使用了INNER JOIN,因此子查询可能在这里不起作用 . 有任何想法吗?
2 回答
在SQL Server中,您可以使用窗口函数执行此操作:
这也应该有效(未经测试)