Sql question (rollup?)

General
1

I have the following table (postgresql 12)…

select version();
CREATE TABLE articles (
  id integer,
  category character varying,
  qty double precision,
  description character varying
);

INSERT INTO articles (id, category, qty, description) VALUES (1, 'c1', 3,  'Article 1');
INSERT INTO articles (id, category, qty, description) VALUES (2, 'c1', 8,  'Article 2');
INSERT INTO articles (id, category, qty, description) VALUES (3, 'c1', 2,  'Article 3');

INSERT INTO articles (id, category, qty, description) VALUES (4, 'c2', 11, 'Article 4');
INSERT INTO articles (id, category, qty, description) VALUES (5, 'c2', 89, 'Article 5');
INSERT INTO articles (id, category, qty, description) VALUES (6, 'c2', 23, 'Article 6');

INSERT INTO articles (id, category, qty, description) VALUES (7, 'c3', 36, 'Article 7');
INSERT INTO articles (id, category, qty, description) VALUES (8, 'c3', 24, 'Article 8');
INSERT INTO articles (id, category, qty, description) VALUES (9, 'c3', 9,  'Article 9');

SELECT * FROM articles;













id
category
qty
description






1
c1
3
Article 1




2
c1
8
Article 2




3
c1
2
Article 3




4
c2
11
Article 4




5
c2
89
Article 5




6
c2
23
Article 6




7
c3
36
Article 7




8
c3
24
Article 8




9
c3
9
Article 9
















db<>fiddle CLICK HERE TO SEE THE FIDDLE




… which i want to convert it into this…




expected_result




i have tried to use rollup, but no luck

I would appreciate any help, thanks

            


            

              
              
              
            


            

                
                
              


          

          

            

              



              
                  
                  
              2
              
            

            

              
somehow distinct category and sum qty

            


            

              
              
              
            


            

                
                
              


          

          

            

              



              
                  
                  
              3
              
            

            

              
While I did this with SQLite, the query should be nearly the same:


SELECT * FROM (SELECT NULL as id, category, SUM(qty) as qty, NULL as description FROM articles GROUP BY category UNION SELECT * FROM articles) ORDER BY category, description

            


            

              
              
              2 Likes
            


            

                
                
              


          

          

            

              



              
                  
                  
              4
              
            

            

              
hello greg

thanks for your SQL sorting

she is very good

I did not know that this was possible

Rudolf

example: SQL- groupsort


https://www.dropbox.com/s/1du21i61um6ib60/forum-sqlsort.xojo_binary_project?dl=1