CODEWITHSUNDEEP
sql-servergroup-by
Gohan
Gohan

Reputation: 63

SQL How to group the rows correctly

I have three tables:

**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)

Stake : decimal

Bookie : int (Foreign Key)

Wagers table is not entire but for the sql these columns are sufficient.

I tried this sql code so far:

SELECT Bookie.Bookie, SUM(Amount), Wagers.Stake FROM Transactions
JOIN Bookie on Transactions.Bookie = Bookie.Id
JOIN Wagers on Bookie.Id = Wagers.Bookie
GROUP BY Bookie.Bookie, Wagers.Stake

But my output looks like this:

Bookie | Amount | Stake
William Hill | 600.00 | 14.70
Bet at Home | 500.00 | 30.00
Bet at Home | 500.00 | 45.00
Bet at Home | 500.00 | 50.00
Bet365 | 10200.00 | 100.00

It should look like this:

Bookie | Amount | Stake
William Hill | 600.00 | 14.70
Bet at Home | 500.00 | 125.00
Bet365 | 10200.00 | 100.00

Does anyone know how to achieve this issue?

Thanks

Upvotes: 1

Views: 149

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

With aggregates from two different tables it is best to pre-aggregate the data and only then join:

select 
  b.bookie, 
  t.sum_amount, 
  w.sum_stake
from bookie b 
join
(
  select bookie, sum(amount) as sum_amount
  from transactions
  group by bookie
) t on t.bookie = b.id
join       
(
  select bookie, sum(stake) as sum_stake
  from wagers 
  group by bookie
) w on w.bookie = b.id;

As you are only interested in one aggregation from each table, you can even use correlated subqueries in the select clause instead. Easy to write and easy to read :-)

select 
  b.bookie, 
  (select sum(t.amount) from transactions t where t.bookie = b.id),
  (select sum(w.stake) from wagers w where w.bookie = b.id)
from bookie b

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

You need to aggregate Stake:

SELECT Bookie.Bookie, SUM(Amount) AS Amount, SUM(Wagers.Stake) AS Stake
FROM Transactions
JOIN Bookie 
  ON Transactions.Bookie = Bookie.Id
JOIN Wagers 
  ON Bookie.Id = Wagers.Bookie
GROUP BY Bookie.Bookie;

EDIT:

SELECT Bookie, MAX(Amount) AS Amount, SUM(Stake) AS Stake
FROM (
  SELECT Bookie.Bookie, SUM(Amount) AS Amount, Wagers.Stake 
  FROM Transactions
  JOIN Bookie on Transactions.Bookie = Bookie.Id
  JOIN Wagers on Bookie.Id = Wagers.Bookie
  GROUP BY Bookie.Bookie, Wagers.Stake
) AS sub
GROUP BY Bookie;

Upvotes: 2

Related Questions