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
