Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Posts
    2

    Unanswered: help with group by clause

    how do you group by month or year or quarter in pl/sql select statement??

  2. #2
    Join Date
    Jan 2004
    Location
    Melbourne, Victoria, Australia
    Posts
    6
    Hi

    Try
    seletc ...
    trunc (date_col,'MM') in select and then group by

  3. #3
    Join Date
    Jan 2004
    Location
    Melbourne, Victoria, Australia
    Posts
    6
    Hi

    Sorry for the incomplete previous posting -
    This is what I meant:

    select ...
    trunc(date_col,'MM') as month_trunc,
    ...
    from ...
    where ...
    group by ...
    trunc(date_col,'MM')
    ...

    Also you can use trunc (date_col,'YYYY') to group by year and trunc (date_col, 'Q') to group by quarter

    HTH

    Kind regards

    Kresimir Fabijanic

  4. #4
    Join Date
    Jan 2004
    Posts
    2

    dint work

    hi! thanx
    but it did not work
    if i want to show the sales quantity by month
    then how to go about it ??

    select storeid, to_char(ord_date,'mm') as month, sum(salesqty)
    from sales
    group by store_id, trunc(ord_date,'mm');

    this did not work
    any ideas ???

  5. #5
    Join Date
    Jan 2004
    Posts
    370

    Re: dint work

    Originally posted by oracleworker
    hi! thanx
    but it did not work
    if i want to show the sales quantity by month
    then how to go about it ??

    select storeid, to_char(ord_date,'mm') as month, sum(salesqty)
    from sales
    group by store_id, trunc(ord_date,'mm');

    this did not work
    any ideas ???

    select storeid, to_char(ord_date, 'MONTH') month, sum(salesqty)
    from sales
    group by storeid, to_char(ord_date, 'MONTH')
    order by deptno, to_date(month,'MM')

    Replace MONTH with YYYY or Q to group and order by year and quarter

  6. #6
    Join Date
    Jan 2004
    Posts
    370
    Correction:

    select storeid, to_char(ord_date, 'MONTH') month, sum(salesqty)
    from sales
    group by storeid, to_char(ord_date, 'MONTH')
    order by storeid, to_date(month,'MM')

    (order by clause had wrong column name)

  7. #7
    Join Date
    Jan 2004
    Location
    Melbourne, Victoria, Australia
    Posts
    6
    Hi

    I still suggest you try trunc instead of to_char

    Kind regards

    Kresimir Fabijanic

  8. #8
    Join Date
    Jan 2004
    Location
    Melbourne, Victoria, Australia
    Posts
    6
    Hi

    This is what I had in mind

    select storeid,
    trunc(ord_date,'mm') as month,
    sum(salesqty)
    from sales
    group by store_id,
    trunc(ord_date,'mm');

    Similar query (different columns and tables) works OK for my sales transaction table.

    HTH

    Kind regards

    Kresimir Fabijanic

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •