I have a table in the below format:

Reproduce code:

CREATE TEMP TABLE reasons
(
Reasons INT ,
Counts_students VARCHAR(50) ,
total_count int
);

INSERT INTO reasons
VALUES ( 'A',2,null),
( 'B',3,null),
('C',8,null),
('D',1,null),
('E',2,null),
('F',5,null),
(NULL,NULL,45)

The final output introduces a new row in the "Reasons" category as "rest", which is essentially the total across the total_count- count_students

Any help is truly appreciated! Thank you.

CodePudding user response：

You use the the tow result columns for all rows that have reasons that are no NULL and then UNION ALL a simple SELECT with tow aggregation functions

SELECT reasons,Counts_students FROM reasons WHERE reasons IS NOT NULL
UNION SELECT 'Rest', (SELECT MAX(total_count) - SUM(Counts_students) FROM reasons)

reasons | Counts_students
:------ | :--------------
A       | 2              
B       | 3              
C       | 8              
D       | 1              
E       | 2              
F       | 5              
Rest    | 24             

db<>fiddle here

As Ergest Basha pointed ou the subselect isn't really necessary

SELECT reasons,Counts_students 
FROM reasons 
WHERE reasons IS NOT NULL
UNION 
SELECT 'Rest', MAX(total_count) - SUM(Counts_students) 
FROM reasons

reasons | Counts_students
:------ | :--------------
A       | 2              
B       | 3              
C       | 8              
D       | 1              
E       | 2              
F       | 5              
Rest    | 24             

db<>fiddle here