DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: need help with grouping sets in sql - oracle

  1. #1
    Join Date
    Aug 2007
    Posts
    4

    Unhappy 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.........

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    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.

  3. #3
    Join Date
    Aug 2007
    Posts
    4
    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

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Use ROLLUP fn to get the column total.

  5. #5
    Join Date
    Aug 2007
    Posts
    4
    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

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135

  7. #7
    Join Date
    Aug 2007
    Posts
    4
    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
  •  


Click Here to Expand Forum to Full Width