SQL, Find players total winnings separated into area

Question

I need to find the total prize money each contestant has won sorted into the State they live in and show the States total "winnings". Note that each lottery can have multiple winners and payout is evenly divided between them.

This is what I've come up with so far:

select State.Name, SUM(Lottery.Payout) 
from State, Lottery
join Player on(Player.CountyID = County.CountyID) join County on(County.State = State) group by State.Name;

select * from State_TotalWinnings;

With my "solution" i get the total prize money recursively added for each county in each state. And i can't for the life of me figure out how to stop this recursive problem.

Innlandet|761340435
Møre og Romsdal|761340435
Nordland|761340435
Oslo|761340435
Rogaland|761340435
Viken|761340435

My database is set up as follows:

CREATE TABLE State(
StateID char(2),
Population INT,
Name varchar(128),
PRIMARY KEY (StateID)
);

CREATE TABLE County(
CountyID char(4),
State char(2),
Population INT,
Area decimal(10,2),
Name varchar(128),
PRIMARY KEY (CountyID),
FOREIGN KEY (State) REFERENCES State (StateID)
);

CREATE TABLE Player(
PlayerNr INT,
Name varchar(128),
Address varchar(128),
CountyID  char(4),
PRIMARY KEY (PlayerNr),
FOREIGN KEY (CountyID) REFERENCES County (CountyID)
);

CREATE TABLE Employee(
EmployeeID INT,
Name varchar(128),
CountyID  char(4),
PRIMARY KEY (EmployeeID),
FOREIGN KEY (CountyID) REFERENCES County (CountyID)
);

CREATE TABLE Lottery(
LotteryID INT,
Date DATE,
Payout INT,
EmployeeID INT,
PRIMARY KEY (LotteryID),
FOREIGN KEY (EmployeeID) REFERENCES Employee (EmployeeID)
);

CREATE TABLE Winner(
PlayerNr INT,
LotteryID INT,
PRIMARY KEY (PlayerNr,LotteryID),
FOREIGN KEY (PlayerNr) REFERENCES Player (PlayerNr),
FOREIGN KEY (LotteryID) REFERENCES Lottery (LotteryID)
);

David_Maddox_673803 · Answer

The "sophisticated" piece of this puzzle is dividing the winnings evenly amongst the winners. I use a CTE in my example (that "with" clause at the beginning).

with winning_payouts as (
  select w.LotteryID, payout/count(*) as payout
  from Lottery l
  join Winner w using (LotteryID)
  group by w.LotteryID
)

select s.Name, sum(payout)
from State s
join County c using (State)
join Player p using (CountyID)
join Winner w using (PlayerNr)
join winning_payouts using (LotteryID)
group by State.Name

There are a number of other ways to do this that don't include a CTE, including:

Making the CTE its own, standalone view to make future queries that much easier.
Using a sub-query instead of a CTE
Using a window function such as payout/count(*) over (partition by LotteryID)

SQL, Find players total winnings separated into area

1 Answers