Complex Query and SUM

[xProteuSx]

I have a database, that contains two kinds of member accounts: primary, and secondary accounts (parents and children).  All the info for both classes are stored in the same table, with a field called usr_parent = 0 for children and usr_parent = 1 for parents.  Also, each user has a primary id field called usr_id.

 

Each user also has, in the same table, a column called usr_money.  However, only children can have a positive balance, and all parent accounts stay at a default value of 0.

 

Also, each child has a field called usr_family with the usr_id of the parent (primary account) being stored here as a foreign key.

 

So, for example:

 

table1

 

USR_ID | USR_PARENT | USR_MONEY | USR_FAMILY

 

1 | 1 | 0 | NULL = parent account with primary id 1, 0 money, and a null family field

2 | 0 | 12 | 1 = child account with primary id 2, 12 money, and parent id 1 (first child of usr_id 1)

3 | 0 | 10 | 1  = child account with primary id 3, 10 money, and parent id 1 (second child of usr_id 1)

 

What I am trying to do is to get all of the info from the table, as well as a SUM of all sub-account balances for anyone who is a parent!

 

Something like this (???):

 

SELECT * FROM table1 SUM(USR_MONEY) WHERE USR_ID = USR_FAMILY AND USR_PARENT = 1

 

Essentially, this is what I would like returned:

 

Record 1:  1 | 1 | 0 | NULL | 22

Record 2:  1 | 1 | 12| NULL | NULL

Record 3:  1 | 1 | 10 | NULL | NULL

 

Records 2 and 3 do not contain the 5th field, because they are child accounts (USR_PARENT = 0) but Record 1 has the sum of the USR_MONEY fields for all users where USR_FAMILY is equal to the USR_ID of the parent account.

 

Thanks in advance.

[xProteuSx]

Actually, I could also use the child count as well for the parent account.  So, in this case, the resulting query data should read like this:

 

Record 1:  1 | 1 | 0 | NULL | 22 | 2  (sum of 22 from records 2 and 3, field[2], and count of 2 records where field[3] is equal to USR_ID from original table)

Record 2:  1 | 1 | 12| NULL | NULL | NULL

Record 3:  1 | 1 | 10 | NULL | NULL | NULL

 

???

[fenway]

"a SUM of all sub-account balances for anyone who is a parent"

 

Is that a simply SUM(), GROUP BY, where usr_parent = 0?

[xProteuSx]

I ended up accomplishing what I needed by creating an array and producing a series of while, if, and for loops to sum and populate that data.  However, I would still love to figure out how to accomplish the same task using SUM and GROUP BY.  I tried, but I have never used SUM or GROUP BY before, and I could not get my query to work.

[fenway]

Try:

select table1.USR_ID, table1.USR_PARENT, table1.USR_MONEY, table1.USR_FAMILY, balances.childSum
FROM table1
LEFT JOIN 
(
select USR_FAMILY, sum( USR_MONEY) AS childSum
from table1 
where USR_PARENT = 0
group by USR_FAMILY
)
AS balances
USING ( USR_FAMILY )

 

Untested -- should be close.