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? 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
