Lyssa - 6 months ago 27

SQL Question

I have a SQL query that takes several data points from each day across a window of time in a UNION ALL query, and then sums that data for each day in the result set.

What I want to add to the result set is a total at the bottom of ALL the days in the result set. I have tried to use ROLLUP (with the GROUP BY statement) with no success, and I keep getting the error

`ORA-00933: SQL command not properly ended`

Not sure if there is a trick to ROLLUP with a UNION ALL, of if it will not work at all. I have also tried a temp table, and then perform a UNION ALL with the sum of the temp table, but this seems so complicated (and I have not been able to get it to work either).

Can someone please help guide me? Here is my working code:

`SELECT /*+ USE_HASH(x) */`

ACTIVITY_DAY

, SUM(ORDERED_UNITS)

, SUM(ORDERED_AMT)

FROM (

SELECT ACTIVITY_DAY,ORDERED_UNITS,ORDERED_AMT FROM D_DAILY_ORDERS

WHERE REGION_ID = 1

AND MARKETPLACE_ID = 1

AND ACTIVITY_DAY BETWEEN TO_DATE('20160409','YYYYMMDD') AND TO_DATE('20160505','YYYYMMDD')

AND ID IN ('B019433MGC', 'B019433O4C', 'B00STUX0F0', 'B017SL40S6', 'B00ZHJDJ5W', 'B00STUX276', 'B017SL44RI', 'B00ST415S0', 'B00ST414O0')

UNION ALL

SELECT ACTIVITY_DAY,ORDERED_UNITS,ORDERED_AMT FROM D_DAILY_ORDERS

WHERE REGION_ID = 1

AND MARKETPLACE_ID = 1

AND ACTIVITY_DAY BETWEEN TO_DATE('20160501','YYYYMMDD') AND TO_DATE('20160505','YYYYMMDD')

AND ID IN ('B007V4YR5M', 'B00BHQ9CHI')

) x

GROUP BY ACTIVITY_DAY

ORDER BY ACTIVITY_DAY

;

Answer

If I understand well, ROLLUP is the right way; you were probably having some syntax error:

```
SQL> SELECT
2 ACTIVITY_DAY
3 , SUM(ORDERED_UNITS)
4 , SUM(ORDERED_AMT)
5 FROM (
6 select sysdate as ACTIVITY_DAY, 1 as ORDERED_UNITS, 10 as ORDERED_AMT from dual union all
7 select sysdate-1 as ACTIVITY_DAY, 2 as ORDERED_UNITS, 20 as ORDERED_AMT from dual union all
8 select sysdate-1 as ACTIVITY_DAY, 2 as ORDERED_UNITS, 20 as ORDERED_AMT from dual
9 ) x
10 GROUP BY rollup(ACTIVITY_DAY)
11 ORDER BY ACTIVITY_DAY
12 ;
ACTIVITY_ SUM(ORDERED_UNITS) SUM(ORDERED_AMT)
--------- ------------------ ----------------
15-MAG-16 4 40
16-MAG-16 1 10
5 50
```