How to add sum of a column as a new row after a group in SQL select query

Question

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.

Answer 1

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

rextester

Answer 2

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.

Answer 3

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 :)