asked
How to aggregate multiple child columns with parent column only once when not grouping by parent?
I have two tables where a parent can have multiple children. I need to SUM a field from the children, and add (exactly once) another field from the parent, grouping by a third field that can be shared by multiple parents. However, I am running into difficulty with this, because the grouping criteria do not include the unique parent id. My query needs to be compatible with SQL Server 2005. I also prefer not to use cursors, or temporary tables unless absolutely necessary, because these tables may be quite large. The below query gives a minimal example of what I am trying to accomplish. -- START setup for query declare @parent table ( Id int, --this is unique paidAmount money, Store int ) declare @child table ( ParentId int, chargedAmount money ) insert into @parent SELECT 1,30,1 UNION ALL SELECT 2,20,1 UNION ALL SELECT 3,30,2 UNION ALL SELECT 4,49,2 insert into @child SELECT 1,15 UNION ALL SELECT 1,20 UNION ALL SELECT 2,50 UNION ALL SELECT 3,60 UNION ALL SELECT 4, 70 select * from @parent p inner join @child c on c.ParentId =
p.Id --END setup for query --under counts the parent paidAmount --(though with different data, it could just as easily overcount) select Store, SUM(chargedAmount) + MAX(paidAmount) from @parent p inner join @child c on c.ParentId =
p.Id group by Store --Over counts the parent paidAmount select Store, SUM(chargedAmount + paidAmount) from @parent p inner join @child c on c.ParentId =
p.Id group by Store --Sum of amounts from group by Store query should equal this (344) select ((SELECT SUM(chargedAmount) from @child)) + ((SELECT SUM(paidAmount) from @parent))
Just to confirm, the rows in @child relate to the parentID not the StoreID?
Right, so does @Fatherjacks answer, with my modification answer your question then? Even if not, it seems to me that it's worth an upvote.
I think this is what you are asking for but you may correct me... I have included the create script I used so there is more data but only run that section once if you want reproducible totals GO -- START setup for query DECLARE @parent TABLE ( Id INT IDENTITY , --this is unique paidAmount MONEY , Store INT ) DECLARE @child TABLE ( ParentId INT , chargedAmount MONEY ) WHILE ( SELECT COUNT(*) FROM @parent ) < 500 BEGIN INSERT INTO @parent ( [paidAmount] , [Store] ) VALUES ( ABS(CHECKSUM(NEWID())) % 10 * 10 + 1 , -- paidAmount - money ABS(CHECKSUM(NEWID())) % 5 + 1 -- Store - int ) END WHILE ( SELECT COUNT(*) FROM @child ) < 8000 BEGIN INSERT INTO @child ( [ParentId] , [chargedAmount] ) VALUES ( ABS(CHECKSUM(NEWID())) % 50 + 1 , -- ABS(CHECKSUM(NEWID())) % 10 * 20 + 1 -- ) END -- END setup for query ; WITH Store AS ( SELECT [p].[Store] , SUM(paidamount) SumStoreAmnt FROM @parent AS p GROUP BY [p].[Store] ), Child AS ( SELECT [c].[ParentId] , SUM([c].[chargedAmount]) AS SumChildAmnt FROM @child AS c GROUP BY [c].[ParentId] ) SELECT [Store].[Store] , [Store].[SumStoreAmnt] , [Child].[SumChildAmnt] , [Child].[SumChildAmnt] + [Store].[SumStoreAmnt] AS RqdTotal FROM [store] INNER JOIN [Child] ON [Child].[ParentId] = [Store].[Store] This runs in about 3s on my server - how many rows are you expecting to be working with?
Should it be this? I.e. linking store to store, rather than parentID to store? ; WITH Store AS ( SELECT [p].[Store] , SUM(paidamount) SumStoreAmnt FROM @parent AS p GROUP BY [p].[Store] ), Child AS ( SELECT [p].[Store] , SUM([c].[chargedAmount]) AS SumChildAmnt FROM @child AS c INNER JOIN @parent p ON [p].[Id] = [c].[ParentId] GROUP BY [p].[Store] ) SELECT [Store].[Store] , [Store].[SumStoreAmnt] , [Child].[SumChildAmnt] , [Child].[SumChildAmnt] + [Store].[SumStoreAmnt] AS RqdTotal FROM [store] INNER JOIN [Child] ON [Child].[Store] = [Store].[Store]
thats what I asked in the comment - the OP shows an example as `select * from @parent p inner join @child c on c.ParentId =
p.Id` which I think may be incorrect
um, being thick and not much time today - are you saying I need to edit the join in my answer?
The join and the child CTE so that it retrieves the store number associated with each ParentID... But equally I may be wrong...
Take à look at OVER clause and PARTITION BY, maybe that can help you
