I have a select query which returns a couple of rows grouped by ParentId. How can I add a new row with sum of a column after each parentId group?
For now I have kept the data in a temp table and the result is as below.
And I want to add a new row at the end of each ParentId group as below with the sum of column LoanAmount.
Any help will be appreciated. Many thanks.
You can use a common table expression to achieve this. Here I've created a cte with rank column for getting it sorted in order.
;WITH cte AS
(SELECT ParentId,
sum(LoanAmount) LoanAmount,
max(rank) + 1 AS rank
FROM test
GROUP BY ParentId)
SELECT *
FROM test
UNION ALL
SELECT *
FROM cte
ORDER BY ParentId, rank
You can make sum of group by subquery then combine them in union
; with cte as
( select 9999 as Slno, Level, ParentId, Ent_id, relation, sum(colname) as colname from table group by Level, ParentId, Ent_id, relation)
, ct as ( select row_number() over (partition by ParentId order by level) as Slno, Level, ParentId, Ent_id, Name, --- rest of your column names
colname from table
union all
select Slno, Level, ParentId, Ent_id, '' as Name, ---rest of '' for each column with column name as alias
colname from cte )
select Slno, Level, ParentId, Ent_Id, Name, ---- your columns of table
colname from ct order by Slno
This is just rough idea. Feel free to ask for any confusion.
Post your exact schema for accurate details.
See this link here enter link description here
I think you want:
SELECT ParentID, SUM(VALUE1), SUM(VALUE2)
FROM tableName
GROUP BY ID
You cant do it after each group or at the bottom like in excel, but you create a 'new table' in your query effectively.
Yeah having seen your updated comment, you main issue is youre thinking of it like excel, SQL is not a spreadsheet tool - its a relational database. Id suggest going through a SQL intro - youll pick up the concepts quite fast.
The query I gave you could be created as a stored procedure.
If you feel I've answered your question, id appreciate an upvote :)
