create table coaches (
coachID integer primary key autoincrement,
coach_name text
);
create table players (
playerID integer primary key autoincrement,
playerName text not null,
salary integer,
fk_coach integer,
constraint abc foreign key (fk_coach) references coaches (coachID)
);
create table matches (
matchID integer primary key autoincrement,
playedAgainst text not null,
totalScore integer not null,
bets real not null
);
create table players_matches (
pmID integer primary key autoincrement,
fk_match integer,
fk_player integer,
constraint mno foreign key (fk_player) references players (playerID),
constraint xyz foreign key (fk_match) references matches (matchID)
);
-- three coach_names CHECKED
insert into coaches (coach_name) values ("coach-1"), ("coach-2"), ("coach-3");
-- four players assigned to each coach CHECKED
insert into players (playerName, salary, fk_coach) values
("ella", 71, 1), ("alexander", 12, 1), ("eliza", 3, 1),
("theo", 91, 1), ("amelia", 82, 2), ("sebastian", 23, 2),
("louis", 53, 2), ("henry", 85, 2), ("penlope", 75, 3),
("james", 36, 3), ("nora", 46, 3), ("julian", 38, 3);
-- 3, 2, 3 matches in those coach_names
insert into matches (playedAgainst, totalScore, bets) values
("Utrecht University", 0, 129),
("Wesleyan University", 2, 112),
("City University of New York", 4, 118),
("Imperial College London", 1, 122),
("University of Göttingen", 2, 104),
("University of California–Davis", 1, 167),
("Dartmouth College", 3, 156),
("University of Sydney", 3, 146);
-- summary
insert into players_matches (fk_match, fk_player) values
(1, 1), (1, 3), (2, 2), (2, 3), (3, 4), (3, 3), (4, 6), (4, 7),
(5, 8), (5, 6), (6, 12), (6, 9), (7, 11), (7, 9), (8, 11), (8, 12);
analyze;
WITH MyMatches (matchID, playedAgainst, totalScore, bets, totalSalary)
as (SELECT matchID, playedAgainst, totalScore, bets, (select sum(salary)
from players,
players_Matches
where
players_matches.fk_player == players.playerID
and
players_matches.fk_match == matches.matchID)
FROM matches)
select coach_name,
matches.matchid as match_number,
matches.playedagainst as match,
playerName as player,
salary,
totalsalary - bets
from players, mymatches as matches, players_matches, coaches
where players.playerID == players_matches.fk_player
and matches.matchID == players_matches.fk_match
and players.fk_coach == coaches.coachID
;
QUERY PLAN
|--SCAN TABLE players_matches (~16 rows)
|--SEARCH TABLE players USING INTEGER PRIMARY KEY (rowid=?) (~1 row)
|--SEARCH TABLE matches USING INTEGER PRIMARY KEY (rowid=?) (~1 row)
|--SEARCH TABLE coaches USING INTEGER PRIMARY KEY (rowid=?) (~1 row)
`--CORRELATED SCALAR SUBQUERY 1
|--SCAN TABLE players_Matches (~4 rows)
`--SEARCH TABLE players USING INTEGER PRIMARY KEY (rowid=?) (~1 row)
coach_name match_number match player salary
totalsalary - bets
---------- ------------ ------------------ ---------- ----------
------------------
coach-1 1 Utrecht University ella 71 -55.0
coach-1 1 Utrecht University eliza 3 -55.0
coach-1 2 Wesleyan Universit alexander 12 -97.0
coach-1 2 Wesleyan Universit eliza 3 -97.0
coach-1 3 City University of theo 91 -24.0
coach-1 3 City University of eliza 3 -24.0
coach-2 4 Imperial College L sebastian 23 -46.0
coach-2 4 Imperial College L louis 53 -46.0
coach-2 5 University of Gött henry 85 4.0
coach-2 5 University of Gött sebastian 23 4.0
coach-3 6 University of Cali julian 38 -54.0
coach-3 6 University of Cali penlope 75 -54.0
coach-3 7 Dartmouth College nora 46 -35.0
coach-3 7 Dartmouth College penlope 75 -35.0
coach-3 8 University of Sydn nora 46 -62.0
coach-3 8 University of Sydn julian 38 -62.0
Creating the missing indexes:
sqlite> .lint fkey-indexes
CREATE INDEX 'players_fk_coach' ON 'players'('fk_coach'); --> coaches(coachID)
CREATE INDEX 'players_matches_fk_match' ON 'players_matches'('fk_match'); -->
matches(matchID)
CREATE INDEX 'players_matches_fk_player' ON 'players_matches'('fk_player'); -->
players(playerID)
sqlite>
sqlite> CREATE INDEX 'players_fk_coach' ON 'players'('fk_coach'); -->
coaches(coachID)
sqlite> CREATE INDEX 'players_matches_fk_match' ON
'players_matches'('fk_match'); --> matches(matchID)
sqlite> CREATE INDEX 'players_matches_fk_player' ON
'players_matches'('fk_player'); --> players(playerID)
sqlite> analyze;
sqlite> WITH MyMatches (matchID, playedAgainst, totalScore, bets, totalSalary)
...> as (SELECT matchID, playedAgainst, totalScore, bets, (select
sum(salary)
...> from
players, players_Matches
...> where
players_matches.fk_player == players.playerID
...> and
players_matches.fk_match == matches.matchID)
...> FROM matches)
...> select coach_name,
...> matches.matchid as match_number,
...> matches.playedagainst as match,
...> playerName as player,
...> salary,
...> totalsalary - bets
...> from players, mymatches as matches, players_matches, coaches
...> where players.playerID == players_matches.fk_player
...> and matches.matchID == players_matches.fk_match
...> and players.fk_coach == coaches.coachID
...> ;
QUERY PLAN
|--SCAN TABLE players_matches (~16 rows)
|--SEARCH TABLE players USING INTEGER PRIMARY KEY (rowid=?) (~1 row)
|--SEARCH TABLE matches USING INTEGER PRIMARY KEY (rowid=?) (~1 row)
|--SEARCH TABLE coaches USING INTEGER PRIMARY KEY (rowid=?) (~1 row)
`--CORRELATED SCALAR SUBQUERY 1
|--SEARCH TABLE players_Matches USING INDEX players_matches_fk_match
(fk_match=?) (~2 rows)
`--SEARCH TABLE players USING INTEGER PRIMARY KEY (rowid=?) (~1 row)
coach_name match_number match player salary
totalsalary - bets
---------- ------------ ------------------ ---------- ----------
------------------
coach-1 1 Utrecht University ella 71 -55.0
coach-1 1 Utrecht University eliza 3 -55.0
coach-1 2 Wesleyan Universit alexander 12 -97.0
coach-1 2 Wesleyan Universit eliza 3 -97.0
coach-1 3 City University of theo 91 -24.0
coach-1 3 City University of eliza 3 -24.0
coach-2 4 Imperial College L sebastian 23 -46.0
coach-2 4 Imperial College L louis 53 -46.0
coach-2 5 University of Gött henry 85 4.0
coach-2 5 University of Gött sebastian 23 4.0
coach-3 6 University of Cali julian 38 -54.0
coach-3 6 University of Cali penlope 75 -54.0
coach-3 7 Dartmouth College nora 46 -35.0
coach-3 7 Dartmouth College penlope 75 -35.0
coach-3 8 University of Sydn nora 46 -62.0
coach-3 8 University of Sydn julian 38 -62.0
---
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 Keith Medcalf
>Sent: Monday, 18 February, 2019 09:24
>To: SQLite mailing list
>Subject: Re: [sqlite] How to get aggregate without reducing number of
>rows (repeats are ok)?
>
>
>On Monday, 18 February, 2019 02:27, Rocky Ji <rockyji3...@gmail.com>:
>
>>Awesome, thanks for the detailed analysis; this is why I love
>mailing
>>lists.
>
>>It seems you jumped to the meat directly, in the opening of my
>>question,
>>notice the words "scenario" and "given".
>>I am sure you know what they mean, but for record: the links I gave
>>help in
>>"reproducing the problem" in a minimal viable way.
>>Don't worry too much about ROWID, there are no deletes/updates on
>our
>>little example.
>>Finally, please reread the OP, I explicitly mentioned the
>>conditions/relations exactly as they are supposed to be; if the
>words
>>team
>>/ player / coach make you uncomfortable, use abcd...
>
>>Now, can we get the desired result without nested `select`s?
>
>Why? Would you like the prose to not use the words "the" and "then"
>as well?
>
>---
>The fact that there's a Highway to Hell but only a Stairway to Heaven
>says a lot about anticipated traffic volume.
>
>>On Mon, Feb 18, 2019, 10:56 AM Keith Medcalf <kmedc...@dessus.com
>>wrote:
>>
>>>
>>>
>>> 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
>>>
>>_______________________________________________
>>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
