Special Select Statement ( sqlite )

1. Table: "user"

- Userid - Name -

(every userid is unique)

1. Table: "money spend"

- Userid - money spend -

开发者_如何学Python(a userid may have several entries with different "money spend")

Now, I need the total sum of the money spend by a user.

To conclude, I need the following view:

- name - sum (money spend) -

Wich statement may give me this result?

You can use an aggregate function and group by:

select u.name, sum(ms.money) 
from user u, money_spend ms
where u.userid = ms.userid 
group by u.userid

Note that this here assumes that every user has at least 1 row in the money_spend table: http://www.sqlite.org/lang_aggfunc.html

Due to the way that aggregate functions work, you could set up the money_spend table with a 0 value for each user so you don't run into any issues :)

Because you might have users without any entry in table money_spend you need an outer join:

select n.name, sum(ms.money)
from user n
left outer join money_spend ms on (n.userid = ms.userid)
group by n.name

Edit: To be sure this all works I just tried this

create table user(userid, name);
insert into user values (1, 'user1');
insert into user values (2, 'user2');
insert into user values (3, 'user3');
create table moneyspend(userid, amount);
insert into moneyspend values (1,10);
insert into moneyspend values (1,20);
insert into moneyspend values (2,100);
select * from user;
select * from moneyspend;
select u.name, sum(m.amount)
from user u
left outer join moneyspend m on (u.userid = m.userid)
group by u.name;
drop table user;
drop table moneyspend;

The console output is the following (testSQLite is my test DB, testsql.sql is the above)

hols-Mac:div4 hol$ sqlite3 -init testsql.sql testSQLite
-- Loading resources from testsql.sql
1|user1
2|user2
3|user3
1|10
1|20
2|100
user1|30
user2|100
user3|
SQLite version 3.6.12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>