-
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
Last edited by hrishy; 08-16-2007 at 06:14 AM.
-
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
-
-
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
Last edited by sia; 08-16-2007 at 03:39 PM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|