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
[email protected]
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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
