@Luuk that was my initial approach. But everyone advices against nested select statements. Can we do it without that sub-query?
On Sun, Feb 17, 2019, 11:04 PM Luuk <luu...@gmail.com wrote: > > On 17-2-2019 17:46, Rocky Ji wrote: > > 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 > > > C:\TEMP>\util\sqlite3 > SQLite version 3.27.1 2019-02-08 13:17:39 > Enter ".help" for usage hints. > Connected to a transient in-memory database. > Use ".open FILENAME" to reopen on a persistent database. > sqlite> create table test(i int, tekst varchar(20)); > sqlite> insert into test values (1,'a'); > sqlite> insert into test values (2,'b'); > sqlite> insert into test values (3,'c'); > sqlite> insert into test values (4,'a'); > sqlite> select t1.i, t1.tekst, (select sum(t2.i) from test t2 where > t2.tekst=t1.tekst) as totals from test t1; > 1|a|5 > 2|b|2 > 3|c|3 > 4|a|5 > sqlite> > > _______________________________________________ > 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
