I have two tables, one with bank accounts, one which holds groupings of those
accounts, as follows:
Table Accounts
aName
aBalance
Table GroupMembers
gName
aName
What SQL query would total the account balances (aBalance) for all the
accounts (aName) associated with each group (gName)? Each account has its
own group, and can be a member of any number of other groups.
For instance, given the following database, how could I create the results
listed below it?
CREATE TABLE Accounts (aName TEXT(20), aBalance FLOAT);
CREATE TABLE GroupMembers (gName TEXT(20), aName TEXT(20));
INSERT INTO Accounts VALUES ("Visa","100");
INSERT INTO Accounts VALUES ("Savings","125");
INSERT INTO Accounts VALUES ("Cheque","150");
INSERT INTO GroupMembers VALUES ("Visa","Visa");
INSERT INTO GroupMembers VALUES ("Savings","Savings");
INSERT INTO GroupMembers VALUES ("Cheque","Cheque");
INSERT INTO GroupMembers VALUES ("Wells Fargo","Visa");
INSERT INTO GroupMembers VALUES ("Wells Fargo","Savings");
INSERT INTO GroupMembers VALUES ("Wells Fargo","Cheque");
INSERT INTO GroupMembers VALUES ("KeyCard","Cheque");
INSERT INTO GroupMembers VALUES ("KeyCard","Savings");
DESIRED RESULT expressed as gName|total
Visa|100
Savings|125
Cheque|150
KeyCard|275
Wells Fargo|375
The closest I can get to the right results is the following query, but as
you can see below it, it is still very wrong:
select GroupMembers.gName, sum(Accounts.aBalance) from Accounts,
GroupMembers where Accounts.aName in (select GroupMembers.aName from
GroupMembers) group by GroupMembers.gName
Cheque|375
KeyCard|750
Savings|375
Visa|375
Wells Fargo|1125
And now I am out of knowhow. Anyone?
Thanks
John
--
View this message in context:
http://old.nabble.com/Help-with-simple-query-tp29293030p29293030.html
Sent from the SQLite mailing list archive at Nabble.com.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
