Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Ken.

Asked: September 10, 2020 - 10:00 pm UTC

Last updated: September 11, 2020 - 7:48 am UTC

Version: 12C

Viewed 1000+ times

You Asked

Hi Gurus,
I have below query with UNION. it scan same table twice. just wondering if it is possible to re-write without union
WITH T
     AS (SELECT 'AAA' AS DESCR, 100 AS AMT FROM DUAL
         UNION ALL
         SELECT 'BBB', 200 FROM DUAL
         UNION ALL
         SELECT 'BBB', 100 FROM DUAL
         UNION ALL
         SELECT 'BBB', 300 FROM DUAL
         UNION ALL
         SELECT 'CCC', 444 FROM DUAL
         UNION ALL
         SELECT 'CCC', 333 FROM DUAL
         UNION ALL
         SELECT 'DDD', 230 FROM DUAL
         UNION ALL
         SELECT 'DDD', 240 FROM DUAL
         UNION ALL
         SELECT 'EEE', 140 FROM DUAL)
  SELECT CASE WHEN DESCR IN ('BBB', 'CCC', 'DDD') THEN 'PER_NER' ELSE 'BIZ_NER' END AS DES, SUM (AMT)
    FROM T
   WHERE DESCR <> 'AAA'
GROUP BY CASE WHEN DESCR IN ('BBB', 'CCC', 'DDD') THEN 'PER_NER' ELSE 'BIZ_NER' END
UNION ALL
  SELECT DESCR, SUM (AMT)
    FROM T
GROUP BY DESCR;


WITH T
     AS (SELECT 'AAA' AS DESCR, 100 AS AMT FROM DUAL
         UNION ALL
         SELECT 'BBB', 200 FROM DUAL
         UNION ALL
         SELECT 'BBB', 100 FROM DUAL
         UNION ALL
         SELECT 'BBB', 300 FROM DUAL
         UNION ALL
         SELECT 'CCC', 444 FROM DUAL
         UNION ALL
         SELECT 'CCC', 333 FROM DUAL
         UNION ALL
         SELECT 'DDD', 230 FROM DUAL
         UNION ALL
         SELECT 'DDD', 240 FROM DUAL
         UNION ALL
         SELECT 'EEE', 140 FROM DUAL)
  SELECT descr, SUM (AMT)
    FROM T
GROUP BY GROUPING SETS (
             (CASE
                  WHEN DESCR IN ('BBB', 'CCC', 'DDD') THEN 'PER_NER'
                  WHEN  DESCR <> 'AAA' THEN'BIZ_NER'
              END) ,
             DESCR)

I tried to use grouping set like below, but I am not able to fix these two issues: as you can see, 1. the underlined record is extra (need to be removed) ; 2, the column name missed for these two record which is case statement in grouping set.
Is it possible to re-write the query without union?
thanks in advance.

the output like below:
DESCR SUM(AMT)
100
1847
140

CCC 777
DDD 470
EEE 140
BBB 600
AAA 100

expected result is:
DES SUM(AMT)
PER_NER 1847
BIZ_NER 140

CCC 777
DDD 470
EEE 140
BBB 600
AAA 100

and Chris said...

Sure it is.

You need to include the case expression in your select list as well as the group by.

The key is to return this expression when the description is null (e.g. with nvl/coalesce). And exclude the rows where the result of this null from the final results.

For example:

WITH T AS (
  SELECT 'AAA' AS DESCR, 100 AS AMT FROM DUAL
  UNION ALL
  SELECT 'BBB', 200 FROM DUAL
  UNION ALL
  SELECT 'BBB', 100 FROM DUAL
  UNION ALL
  SELECT 'BBB', 300 FROM DUAL
  UNION ALL
  SELECT 'CCC', 444 FROM DUAL
  UNION ALL
  SELECT 'CCC', 333 FROM DUAL
  UNION ALL
  SELECT 'DDD', 230 FROM DUAL
  UNION ALL
  SELECT 'DDD', 240 FROM DUAL
  UNION ALL
  SELECT 'EEE', 140 FROM DUAL
), grps as (
  SELECT COALESCE ( 
           descr,
           CASE
             WHEN DESCR IN ('BBB', 'CCC', 'DDD') THEN 'PER_NER'
             WHEN DESCR <> 'AAA' THEN'BIZ_NER'
           END
         ) descr,
         SUM (AMT)
    FROM T
GROUP BY GROUPING SETS (
             (CASE
                  WHEN DESCR IN ('BBB', 'CCC', 'DDD') THEN 'PER_NER'
                  WHEN  DESCR <> 'AAA' THEN'BIZ_NER'
              END) ,
             DESCR)
)
  SELECT * FROM grps
  WHERE  descr IS NOT NULL;
  
DESCR         SUM(AMT)   
EEE                140 
DDD                470 
AAA                100 
CCC                777 
BBB                600 
PER_NER           1847 
BIZ_NER            140 


Note you don't need grouping sets in this example. Rollup works just fine here.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, September 13, 2020 - 10:39 am UTC

Thanks

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.