Adding one extra class to a column and filling the value in SQL

user_2458552 0
Viewed 32

I have a table in the below format:

enter image description here

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

enter image description here

Any help is truly appreciated! Thank you.

logic mysql sql
1 Answers

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

user_51935360
Related