create table coaches (coach_name text);
create table players (
playerName text not null,
salary integer,
fk_coach text,
constraint abc foreign key (fk_coach) references coaches (coach_name)
);
create table matches (
playedAgainst text not null,
totalScore integer not null,
bets real not null
);
create table players_matches (
fk_match integer,
fk_player integer,
constraint mno foreign key (fk_player) references players (ROWID),
constraint xyz foreign key (fk_match) references matches (ROWID)
);
1) You do not have any columns in either PLAYERS or MATCHES called ROWID.
Foreign Keys may only refer to explicitly named columns.
2) The tables have no PRIMARY KEY. Therefore there is no way to identify a
particular set of attributes in a table (tuple).
3) text fields for playerName and Coach_Name are ill defined (case
sensitive/insensitive? Unique?)
4) Parent Keys should be UNIQUE, Child Keys may be unique (1:1) or not unique
(1:N) (but nevertheless must be indexed)
Your data structure does not model reality. Players are on Teams. Coaches
coach Teams. I suppose you could short-circuit and eliminate Teams by
assigning Players to Coaches, but that means the same coach may never coach
different teams. Matches are between Teams (or coaches players if you have
gotten rid of teams).
Or is this to model school-children at recess, where the so-called team is
merely the happenstance of the "John Picks Chris" and "Alice Picks Don" verses
the next recess where "John Picks Don" and "Alice gets stuck with Chris"?
In any event, why do you not use table generating subqueries that return the
data that you want to play with? In other words, once you know what data you
need then you generate that data.
For example. You say " for a match X, profit/match is `sum(salary of all
players playing in X) - X.bets"
So let us compute that using your wierd and invalid schema:
select matches.rowid as match, (select sum(salary)
from players, players_matches
where players.rowid ==
players_matches.fk_player
and players_matches.fk_match ==
matches.rowid) - matches.bets AS profitmatch
from matches
;
So now you have the "profitmatch" for each match.
The you also want a bunch of detail crap using the same wierd and invalid
schema:
select fk_coach as coach_name,
matches.rowid as match_number,
matches.playedagainst as match,
playerName as player,
salary
from players, matches, players_matches
where players.rowid == players_matches.fk_player
and matches.rowid == players_matches.fk_match
;
And then you want to join those together based on the match:
select fk_coach as coach_name,
matches.rowid as match_number,
matches.playedagainst as match,
playerName as player,
salary,
profitmatch as profit
from players, matches, players_matches,
(
select matches.rowid as match, (select sum(salary)
from players, players_matches
where players.rowid ==
players_matches.fk_player
and players_matches.fk_match ==
matches.rowid) - matches.bets AS profitmatch
from matches
) as profit
where players.rowid == players_matches.fk_player
and matches.rowid == players_matches.fk_match
and profit.match == matches.rowid
;
The SQL is correct and produces the results, however, since the schema is
invalid it is difficult to actually run it. Simplifications are possible
(there is no point in including the "matches" table twice, for example, and the
correlated subquery could be put right in the main query, and if the database
schema itself were normalized, then even further simplifications would be
possible).
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Rocky Ji
>Sent: Sunday, 17 February, 2019 20:19
>To: SQLite mailing list
>Subject: Re: [sqlite] How to get aggregate without reducing number of
>rows (repeats are ok)?
>
>@Keith
>
>Thanks. I am new to SQL and DB in general; please clarify what *is it
>valid* means. How do I check validity of schema?
>
>On Mon, Feb 18, 2019, 1:17 AM Keith Medcalf <kmedc...@dessus.com
>wrote:
>
>>
>> Nice schema. Do you have a valid one?
>>
>>
>> ---
>> The fact that there's a Highway to Hell but only a Stairway to
>Heaven says
>> a lot about anticipated traffic volume.
>>
>>
>> >-----Original Message-----
>> >From: sqlite-users [mailto:sqlite-users-
>> >boun...@mailinglists.sqlite.org] On Behalf Of Rocky Ji
>> >Sent: Sunday, 17 February, 2019 09:47
>> >To: SQLite mailing list
>> >Subject: [sqlite] How to get aggregate without reducing number of
>> >rows (repeats are ok)?
>> >
>> >Hello everyone,
>> >
>> >How can I prevent group by clause from reducing the number of rows
>> >without
>> >affecting accuracy of what aggregate functions provide?
>> >
>> >Scenario:
>> >My club has-many coaches.
>> >Each coach trains a team of players.
>> >Of course, a player has-many matches and a match has-many players.
>> >Given the schema: https://pastebin.com/raw/C77mXsHJ
>> >and sample data: https://pastebin.com/raw/GhsYktRS
>> >
>> >I want a result like: https://pastebin.com/raw/stikDvYS
>> >
>> >NOTE: for a match X, profit/match is `sum(salary of all players
>> >playing in
>> >X) - X.bets`
>> >
>> >To get the result, here's what I came up with:
>> >https://pastebin.com/ckgicBWS
>> >
>> >If I un-comment those lines, I get the profit column but rows are
>> >reduced,
>> >how can I prevent that?
>> >
>> >Thanks,
>> >Rocky.
>> >_______________________________________________
>> >sqlite-users mailing list
>> >sqlite-users@mailinglists.sqlite.org
>> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
