need help with grouping sets in sql - oracle

:unsure:

Hi all,

Please help me...this is my code.....what I also need is a grand total of all the order_total and orders for all the years....from 1999 to 2000....how do I do that?

Code:

---

Select extract(year from order_date) "Year", sales_rep_id "Salesperson", customer_id "Customer", SUM(order_total) "Order Total", count(*) "Orders"
FROM orders
GROUP BY grouping sets (customer_id, sales_rep_id, extract(year from order_Date))
HAVING customer_id BETWEEN 100 AND 105
OR sales_rep_id BETWEEN 155 AND 160
OR extract(year from sysdate) >= extract(year from order_date)
/

the result of this query is:
 
      Year Salesperson  Customer Order Total    Orders
---------- ----------- ---------- ----------- ----------
                              101    190395.1          4
                              104      52092          3
                              105    31902.7          3
                  155              134415.2          5
                  158              156296.2          7
                  160                88238.4          6
      1990                            61655.7          3
      1998                          309929.8        10
      1999                          1274078.8        49
      2000                          252108.3          9

16 rows selected.

---

best regards.........

Hi

grouping sets (customer_id, sales_rep_id, extract(year from order_Date))

shouldnt it also be

grouping sets (customer_id, sales_rep_id, extract(year from order_Date),
order_totals,orders
)

and whatever other combinations you desire

regards
Hrishy

Hi Hrishy,

In first look, including orders in grouping sets makes sense but I need to group only part of the column that shows order data with only year. I have tried various combinations but no luck....

best regards

Use ROLLUP fn to get the column total.

Hi tamilselvan,

I tried using the rollup too...but with having conditions it doesn't work....I mean no errors are generated but query output is same as without using the rollup.

Code:

---

Select extract(year from order_date) "Year", sales_rep_id "Salesperson", customer_id "Customer", sum(order_total) "Order Total", count(*) "Orders"
FROM oe.orders
WHERE order_mode = 'direct'
GROUP BY grouping sets (customer_id, sales_rep_id, rollup(extract(year from order_Date)))
HAVING customer_id BETWEEN 100 AND 105
OR sales_rep_id BETWEEN 155 AND 160
or extract(year from sysdate) >= extract(year from order_date)
/

---

best regards

This may help:

http://www.oracleact.com/papers/rollup.html

Hi,

I actually found out a way to do it couple of minutes back but haven't understood the concept behind using the grouping function in having clause.....and what makes it work in this query.....

Code:

---

Select extract(year from order_date) "Year", sales_rep_id "Salesperson", customer_id "Customer", sum(order_total) "Order Total", count(*) "Orders"
FROM orders
WHERE order_mode = 'direct'
GROUP BY grouping sets (customer_id, sales_rep_id, rollup(extract(year from order_Date)))
HAVING customer_id BETWEEN 100 AND 105
OR sales_rep_id BETWEEN 155 AND 160
or extract(year from sysdate) >= extract(year from order_date)
or grouping (customer_id) > 0
and grouping (sales_rep_id) > 0
/
      Year Salesperson  Customer Order Total    Orders
---------- ----------- ---------- ----------- ----------
                              101    190395.1          4
                              102    69211.4          4
                              103    20591.4          4
                              104      52092          3
                              105    31902.7          3
                  155              134415.2          5
                  156              202617.6          5
                  158              156296.2          7
                  159              151167.2          7
                  160                88238.4          6
      1990                            61655.7          3
      1996                            5546.6          1
      1997                                310          1
      1998                          309929.8        10
      1999                          1274078.8        49
      2000                          252108.3          9
                                    1903629.2        73
17 rows selected.

---

best regards