Sql question (rollup?)

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

somehow distinct category and sum qty

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

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