SQL join in SUM function issue

Question

I want to SUM t.amount, w.bStake and w.lStake (conditionally)

from transactions t and wagers w.

The amount should always be summed up and between back and profit should be a condition.

If w.Profit is positiv -> t.Amount + w.Back

If w.Profit is negativ -> t.Amount + w.Profit

My code that I use until this point looks like this:

SELECT 
  b.Bookie, 
  (SELECT SUM(t.Amount) FROM Transactions t WHERE t.Bookie = b.id)
FROM Bookie b

My tables are:

Bookie:
------------
Id : int (Primary Key)
Bookie : varchar


Transactions:
-------------------
Id : int (Primary Key)
Date : date
Bookie : int (Foreign Key)
Amount : decimal 


Wagers:
Id : int (Primary Key)
Profit : decimal
Back : decimal
Bookie : int (Foreign Key)

How can I solve this Issue? Or is it only possible if I restructure my database? Thanks a lot.

JOIN appropriately then sum(t.Amount + case when w.Profit >=0 then w.Back else w.Profit end) — Alex K.
– Alex K., Commented Jan 4, 2016 at 17:19
@AlexK. my code looks now like this pastebin.com/sVwc3GK0 But I get an error using this code. "Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression." How can I fix this issue? — Gohan
– Gohan, Commented Jan 5, 2016 at 12:36

Nir Levy · Accepted Answer · 2016-01-04 17:26:56Z

1

you need to join the three tables, and then you can use case to get the sum:

SELECT 
  b.Bookie,
  SUM(CASE mySum WHEN If w.Profit >= 0 then t.Amount + w.Back
             ELSE t.Amount + w.Profit)
FROM Transactions as t inner join Bookie as b on t.Bookie = b.id
INNER JOIN Wagers as w on b.id = w.bookie

answered Jan 4, 2016 at 17:26

Nir Levy

13k3 gold badges23 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gohan Over a year ago

I get two Syntax error if I use your code - in near of if-keyword - in near of then-keyword I used the Code in Microsoft SQL Server Management Studio 2014 What is mySum after CASE keyword? Do I have to change mySum?

Gohan Over a year ago

pastebin.com/FgWBgRUa This is the current code, but i get an error: Column 'Bookie.Bookie' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. And if I use the Group by Clause, it would be calculated incorrectly.

Collectives™ on Stack Overflow

SQL join in SUM function issue

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related