May 15, 2012 at 9:26 pm
hi
i have a data base of 2 tables
1-members(memberid, membername, groupid)
2-bills(billid ,memberid,billamount)
bill is joined to members on memberid
and members is joined to it self that some members are in a group of an other member so the child members groupid is joined to the parents member memberid
i want to write a query to get the total bill of each member
so i wrote like this
select memberid sum(billamount) from members join bill group by memberid
but that's not good enough because i want to get the child members total included in the parents total and i can't figure how to do that
can somebody help me
May 15, 2012 at 9:50 pm
Would you please provide us some test data?
It will be a great help to do work around.
May 16, 2012 at 1:14 am
Is this something like what you're after?
DECLARE @members TABLE (memberid INT, membername VARCHAR(20), groupid INT)
DECLARE @bills TABLE (billid INT, memberid INT, billamount MONEY)
INSERT INTO @members
SELECT 1, 'Dwain', NULL
UNION ALL SELECT 2, 'Jeff', NULL
UNION ALL SELECT 3, 'Dwain1', 1
UNION ALL SELECT 4, 'Dwain2', 1
UNION ALL SELECT 5, 'Jeff1', 2
UNION ALL SELECT 6, 'Bobby', NULL
INSERT INTO @bills
SELECT 1, 1, 50
UNION ALL SELECT 2, 1, 25
UNION ALL SELECT 3, 2, 15
UNION ALL SELECT 4, 3, 80
UNION ALL SELECT 5, 4, 70
UNION ALL SELECT 5, 5, 30
UNION ALL SELECT 5, 5, 50
SELECT memberid, membername
,parentbilltotal = ISNULL((
SELECT SUM(billamount)
FROM @bills b
WHERE b.memberid = m.memberid), 0)
,childbilltotal = ISNULL((
SELECT SUM(billamount)
FROM @bills b
INNER JOIN @members m2 ON b.memberid = m2.memberID
WHERE m.memberid = m2.groupid), 0)
FROM @members m
Note that there are various ways you could add the parent and child totals together in case you don't need to see them split like I did.
Also note, that this works only for one level of child. In other words, the children of children won't be added into the childbilltotal of the parent.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply