CODEWITHSUNDEEP
sqlsql-serversum
user8017372
user8017372

Reputation:

Insert SUM combine into select with other column SQL

I have problem with my sum function in sql, which is like this:

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

And the result is:

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

I want to add SUM of all total in the table by using SUM(TOTAL) like this:

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

But then I get an error:

Msg 8120, Level 16, State 1, Line 264
Column 'Member.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

But If I create new select with just sum like this it works:

SELECT SUM(Total) From Order_Member;

It works producing this output:

ColumnName
665

So how can I combine two select in the given, I also used INNER JOIN so subquery might not work here. Any ideas?

Upvotes: 1

Views: 316

Answers (2)

Phil
Phil

Reputation: 42991

This should work as well (not tested)

with cte as
(
    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
)
select *, (select sum(total) from cte) as SumTotal
from cte

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

In SQL Server, you can do this using a window function:

SELECT m.Name, b.Title AS BookBought, omd.Quantity, omd.Price, om.Total,
       SUM(Order_Member.Total) OVER (PARTITION BY m.MemberId) as MemberTotal
FROM Order_Member om INNER JOIN
     OrderWithMember owm
     ON om.OrderID = owm.OrderID INNER JOIN
     Order_Member_Det omd
     ON owm.MemberOrderID = omd.MemberOrderID INNER JOIN
     Member m
     ON owm.MemberID = m.MemberID INNER JOIN
     Book b
     ON omd.BookID = b.BookID;

Upvotes: 0

Related Questions