I have a table and a view that utilize the Oracle ROLLUP function.
Please refer to the details here: dbfiddle.
I wonder if it would be possible to only calculate sums for certain columns using ROLLUP. For instance:
| Year | Customer | Jan | Feb | ... | Dec | |
|---|---|---|---|---|---|---|
| 2023 | Customer1 | Article1 | 1 | 1 | ... | 1 |
| 2023 | Customer1 | Article2 | 1 | 1 | ... | 1 |
| 2023 | Customer1 | Sum | 2 | 2 | ... | 2 |
| 2023 | Customer1 | Article3 | 1 | 1 | ... | 1 |
| 2023 | Customer1 | Article4 | 1 | 1 | ... | 1 |
I would like to have a sum row after Article1 and Article2, but not after Article3 and Article4.
In my example in fiddle, I wish to determine if it is possible to hide the 'Sum' row below the line where 'Bücher- Warensendungen' occurs.
Since mbr_prodgr_id and mbr_prodgr appear to have a one-to-one correlation, you do not appear to need to ROLLUP both of them; just ROLLUP the primary key (presumably mbr_prodgr_id) and find the other with aggregation.
Then you can use:
SELECT year,
gp_gr_id,
mbr_gr_id,
gp_id,
bezirk_id,
mbr_prodgr_id,
CASE
WHEN GROUPING_ID(mbr_prodgr_id) = 1
THEN 'Sum'
ELSE MAX(mbr_prodgr)
END AS mbr_prodgr,
COALESCE(SUM(CASE WHEN month = 4 THEN summe END), 0) AS apr,
COALESCE(SUM(CASE WHEN month = 5 THEN summe END), 0) AS mai,
COALESCE(SUM(CASE WHEN month = 6 THEN summe END), 0) AS juni,
sum(summe) summe_jahr
FROM t
WHERE deleted =0
AND mbr_prodgr_id IS NOT NULL
GROUP BY
year,
gp_gr_id,
mbr_gr_id,
gp_id,
bezirk_id,
ROLLUP(mbr_prodgr_id)
HAVING (GROUPING_ID(mbr_prodgr_id), MAX(mbr_prodgr_id)) NOT IN ((1, 12))
ORDER BY
year,
gp_gr_id,
mbr_gr_id,
gp_id,
bezirk_id,
mbr_prodgr_id
or maybe (depending on if you want to filter using mbr_prodgr_id or mbr_gr_id):
SELECT year,
gp_gr_id,
mbr_gr_id,
gp_id,
bezirk_id,
mbr_prodgr_id,
CASE
WHEN GROUPING_ID(mbr_prodgr_id) = 1
THEN 'Sum'
ELSE MAX(mbr_prodgr)
END AS mbr_prodgr,
COALESCE(SUM(CASE WHEN month = 4 THEN summe END), 0) AS apr,
COALESCE(SUM(CASE WHEN month = 5 THEN summe END), 0) AS mai,
COALESCE(SUM(CASE WHEN month = 6 THEN summe END), 0) AS juni,
sum(summe) summe_jahr
FROM t
WHERE deleted =0
AND mbr_prodgr_id IS NOT NULL
GROUP BY
year,
gp_gr_id,
mbr_gr_id,
gp_id,
bezirk_id,
ROLLUP(mbr_prodgr_id)
HAVING (GROUPING_ID(mbr_prodgr_id), mbr_gr_id) NOT IN ((1, 3))
ORDER BY
year,
gp_gr_id,
mbr_gr_id,
gp_id,
bezirk_id,
mbr_prodgr_id
Which, for the sample data (taken from the fiddle):
CREATE TABLE T (
GP_ID INTEGER,
MBR_GR_ID INTEGER,
BEZIRK_ID INTEGER,
GEBIET_ID INTEGER,
MBR_ID INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY ( START WITH 92541 MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 20 NOORDER NOKEEP NOSCALE) NOT NULL,
MONTH INTEGER,
YEAR INTEGER,
SUMME INTEGER,
GP_GR_ID INTEGER,
DELETED INTEGER,
IS_SUM INTEGER,
MBR_PRODGR VARCHAR2(32 BYTE),
MBR_PRODGR_ID NUMBER
);
insert into t (
MBR_GR_ID, MBR_ID, MONTH, YEAR,
SUMME, GP_GR_ID, DELETED, MBR_PRODGR, MBR_PRODGR_ID
)
SELECT 1, 60884, 5, 2023, 3, 5, 0, 'Standardbrief', 2 FROM DUAL UNION ALL
SELECT 1, 60885, 5, 2023, 1, 5, 0, 'Standardbrief', 2 FROM DUAL UNION ALL
SELECT 1, 60886, 5, 2023, 1, 5, 0, 'Standardbrief', 2 FROM DUAL UNION ALL
SELECT 1, 60887, 5, 2023, 1, 5, 0, 'Standardbrief', 2 FROM DUAL UNION ALL
SELECT 1, 60888, 5, 2023, 2, 5, 0, 'Kompaktbrief', 3 FROM DUAL UNION ALL
SELECT 1, 60889, 5, 2023, 2, 5, 0, 'Maxibrief', 5 FROM DUAL UNION ALL
SELECT 1, 60890, 5, 2023, 2, 5, 0, 'Maxibrief', 5 FROM DUAL UNION ALL
SELECT 1, 60891, 5, 2023, 2, 5, 0, 'Maxibrief', 5 FROM DUAL UNION ALL
SELECT 1, 60892, 5, 2023, 1, 5, 0, 'Standardbrief', 2 FROM DUAL UNION ALL
SELECT 1, 60893, 5, 2023, 1, 5, 0, 'Großbrief', 4 FROM DUAL UNION ALL
SELECT 1, 60894, 5, 2023, 1, 5, 0, 'Standardbrief', 2 FROM DUAL UNION ALL
SELECT 1, 60895, 5, 2023, 2, 5, 0, 'Maxibrief', 5 FROM DUAL UNION ALL
SELECT 2, 60896, 5, 2023, 2, 5, 0, 'Info Standard', 7 FROM DUAL UNION ALL
SELECT 2, 60897, 5, 2023, 2, 5, 0, 'Info Standard', 7 FROM DUAL UNION ALL
SELECT 2, 60898, 5, 2023, 2, 5, 0, 'Info Standard', 7 FROM DUAL UNION ALL
SELECT 2, 60899, 5, 2023, 2, 5, 0, 'Info Standard', 7 FROM DUAL UNION ALL
SELECT 2, 60900, 5, 2023, 2, 5, 0, 'Info Standard', 7 FROM DUAL UNION ALL
SELECT 2, 60901, 5, 2023, 2, 5, 0, 'Info Groß', 9 FROM DUAL UNION ALL
SELECT 1, 60902, 5, 2023, 1, 5, 0, 'Standardbrief', 2 FROM DUAL UNION ALL
SELECT 1, 60903, 5, 2023, 1, 5, 0, 'Standardbrief', 2 FROM DUAL UNION ALL
SELECT 1, 60904, 5, 2023, 1, 5, 0, 'Großbrief', 4 FROM DUAL UNION ALL
SELECT 1, 60905, 5, 2023, 2, 5, 0, 'Maxibrief', 5 FROM DUAL UNION ALL
SELECT 1, 60906, 5, 2023, 2, 5, 0, 'Standardbrief', 2 FROM DUAL UNION ALL
SELECT 1, 60907, 5, 2023, 1, 5, 0, 'Großbrief', 4 FROM DUAL UNION ALL
SELECT 1, 60908, 5, 2023, 1, 5, 0, 'Standardbrief', 2 FROM DUAL UNION ALL
SELECT 1, 60909, 5, 2023, 2, 5, 0, 'Maxibrief', 5 FROM DUAL UNION ALL
SELECT 3, 60910, 5, 2023, 1, 5, 0, 'Bücher- Warensendungen', 12 FROM DUAL UNION ALL
SELECT 1, 60911, 5, 2023, 1, 5, 0, 'Großbrief', 4 FROM DUAL UNION ALL
SELECT 1, 60912, 5, 2023, 2, 5, 0, 'Standardbrief', 2 FROM DUAL UNION ALL
SELECT 1, 60913, 5, 2023, 1, 5, 0, 'Großbrief', 4 FROM DUAL UNION ALL
SELECT 1, 60914, 5, 2023, 1, 5, 0, 'Großbrief', 4 FROM DUAL UNION ALL
SELECT 1, 60915, 5, 2023, 1, 5, 0, 'Standardbrief', 2 FROM DUAL UNION ALL
SELECT 1, 60916, 5, 2023, 1, 5, 0, 'Standardbrief', 2 FROM DUAL UNION ALL
SELECT 1, 60917, 5, 2023, 1, 5, 0, 'Großbrief', 4 FROM DUAL UNION ALL
SELECT 1, 60918, 5, 2023, 2, 5, 0, 'Kompaktbrief', 3 FROM DUAL UNION ALL
SELECT 1, 60919, 5, 2023, 2, 5, 0, 'Maxibrief', 5 FROM DUAL UNION ALL
SELECT 1, 60920, 5, 2023, 2, 5, 0, 'Standardbrief', 2 FROM DUAL UNION ALL
SELECT 1, 60921, 5, 2023, 2, 5, 0, 'Maxibrief', 5 FROM DUAL UNION ALL
SELECT 1, 60922, 5, 2023, 2, 5, 0, 'Kompaktbrief', 3 FROM DUAL UNION ALL
SELECT 3, 60923, 5, 2023, 1, 5, 0, 'Bücher- Warensendungen', 12 FROM DUAL UNION ALL
SELECT 1, 60924, 5, 2023, 1, 5, 0, 'Standardbrief', 2 FROM DUAL UNION ALL
SELECT 3, 60925, 5, 2023, 1, 5, 0, 'Bücher- Warensendungen', 12 FROM DUAL UNION ALL
SELECT 1, 60926, 5, 2023, 1, 5, 0, 'Standardbrief', 2 FROM DUAL UNION ALL
SELECT 1, 60927, 5, 2023, 2, 5, 0, 'Kompaktbrief', 3 FROM DUAL UNION ALL
SELECT 1, 60928, 5, 2023, 1, 5, 0, 'Großbrief', 4 FROM DUAL UNION ALL
SELECT 1, 60929, 5, 2023, 1, 5, 0, 'Standardbrief', 2 FROM DUAL UNION ALL
SELECT 1, 60930, 5, 2023, 1, 5, 0, 'Standardbrief', 2 FROM DUAL UNION ALL
SELECT 1, 60931, 5, 2023, 1, 5, 0, 'Standardbrief', 2 FROM DUAL UNION ALL
SELECT 1, 60932, 5, 2023, 1, 5, 0, 'Großbrief', 4 FROM DUAL UNION ALL
SELECT 1, 60933, 5, 2023, 4, 5, 0, 'Maxibrief', 5 FROM DUAL UNION ALL
SELECT 1, 60934, 5, 2023, 2, 5, 0, 'Kompaktbrief', 3 FROM DUAL UNION ALL
SELECT 1, 60935, 5, 2023, 1, 5, 0, 'Großbrief', 4 FROM DUAL UNION ALL
SELECT 3, 60936, 5, 2023, 1, 5, 0, 'Bücher- Warensendungen', 12 FROM DUAL UNION ALL
SELECT 1, 60937, 5, 2023, 1, 5, 0, 'Standardbrief', 2 FROM DUAL UNION ALL
SELECT 1, 60938, 5, 2023, 6, 5, 0, 'Maxibrief', 5 FROM DUAL UNION ALL
SELECT 1, 60939, 5, 2023, 2, 5, 0, 'Standardbrief', 2 FROM DUAL;
Both outputs:
| YEAR | GP_GR_ID | MBR_GR_ID | GP_ID | BEZIRK_ID | MBR_PRODGR_ID | MBR_PRODGR | APR | MAI | JUNI | SUMME_JAHR |
|---|---|---|---|---|---|---|---|---|---|---|
| 2023 | 5 | 1 | null | null | 2 | Standardbrief | 0 | 27 | 0 | 27 |
| 2023 | 5 | 1 | null | null | 3 | Kompaktbrief | 0 | 10 | 0 | 10 |
| 2023 | 5 | 1 | null | null | 4 | Großbrief | 0 | 10 | 0 | 10 |
| 2023 | 5 | 1 | null | null | 5 | Maxibrief | 0 | 26 | 0 | 26 |
| 2023 | 5 | 1 | null | null | null | Sum | 0 | 73 | 0 | 73 |
| 2023 | 5 | 2 | null | null | 7 | Info Standard | 0 | 10 | 0 | 10 |
| 2023 | 5 | 2 | null | null | 9 | Info Groß | 0 | 2 | 0 | 2 |
| 2023 | 5 | 2 | null | null | null | Sum | 0 | 12 | 0 | 12 |
| 2023 | 5 | 3 | null | null | 12 | Bücher- Warensendungen | 0 | 4 | 0 | 4 |