sum data for quater
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: sum data for quater

  1. #1
    Join Date
    Feb 2001
    Posts
    30
    Hi All,

    I have many transaction data by date such sales.
    I need to sum up sales for quarter in each product.

    How I do?

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    You can decode function.

  3. #3
    Join Date
    Feb 2001
    Posts
    30
    give me an example. pls.

  4. #4
    Join Date
    Aug 2000
    Posts
    462
    fog,

    You didn't provide any structure info, so I guessed that you have a table named mytable with two columns of interest: transdate and transprice.

    select
    decode(to_char(transdate,'MON'),
    'JAN','1st Quarter ',
    'FEB','1st Quarter ',
    'MAR','1st Quarter ',
    'APR','2nd Quarter ',
    'MAY','2nd Quarter ',
    'JUN','2nd Quarter ',
    'JUL','3rd Quarter ',
    'AUG','3rd Quarter ',
    'SEP','3rd Quarter ',
    'OCT','4th Quarter ',
    'NOV','4th Quarter ',
    'DEC','th Quarter '
    ) ||TO_CHAR(TRANSDATE,'YYYY') transquarter,
    sum(transprice)
    from mytable group by
    decode(to_char(transdate,'MON'),
    'JAN','1st Quarter ',
    'FEB','1st Quarter ',
    'MAR','1st Quarter ',
    'APR','2nd Quarter ',
    'MAY','2nd Quarter ',
    'JUN','2nd Quarter ',
    'JUL','3rd Quarter ',
    'AUG','3rd Quarter ',
    'SEP','3rd Quarter ',
    'OCT','4th Quarter ',
    'NOV','4th Quarter ',
    'DEC','th Quarter '
    ) ||TO_CHAR(TRANSDATE,'YYYY')
    order by substr(transquarter,16,19) asc, transquarter asc
    /

  5. #5
    Join Date
    Jul 2000
    Posts
    243
    why not use the "group by" ans sum(_) is the select part of the statment

    select sum(_)
    from ___
    where ___
    grou by ___
    shawish_sababa

    shawish_sababa@hotmail.com

  6. #6
    Join Date
    Aug 2000
    Posts
    462
    Shaw:

    What would you group by? If you group by transdate, then the results are grouped by transdate rather than quarter, and the quarters would not be identified. If I'm not getting your point, please post a complete select statement that I can understand/test.

    Thanks!

    Here's my table:

    SQL> desc mytable
    Name Null? Type
    ----------------------------------------- -------- -----------
    TRANSDATE DATE
    TRANSPRICE NUMBER(6,2)

    SQL> select * from mytable;

    TRANSDATE TRANSPRICE
    --------- ----------
    02-JAN-01 10
    03-JAN-01 10
    04-JAN-01 10
    02-JAN-00 10
    02-JAN-00 10
    02-JAN-00 10
    02-JAN-00 10
    02-JAN-01 10
    02-JAN-01 10
    02-JAN-01 10
    02-JAN-01 10

    TRANSDATE TRANSPRICE
    --------- ----------
    15-SEP-00 10
    15-SEP-00 10
    15-SEP-00 10
    15-SEP-00 10

    15 rows selected.


    Then I run the query I posted earlier:

    TRANSQUARTER SUM(TRANSPRICE)
    ---------------- ---------------
    1st Quarter 2000 40
    3rd Quarter 2000 40
    1st Quarter 2001 70

    As I said above, if I'm not getting your point, please correct me.

    Thanks!

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Oracle provides format model 'Q' for date-to-cahr and char_to_date conversion that we could use to get a quarter the date belongs to.

    SELECT TO_CHAR(transdate,'YYYY') year, TO_CHAR(transdate,'Q') quarter, SUM(sales)
    FROM sales_table
    GROUP BY TO_CHAR(transdate,'YYYY'), TO_CHAR(transdate,'Q');

    So there is no need to use DECODE....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Aug 2000
    Posts
    462
    thanks jmodic! That cleans this up a bunch.

  9. #9
    Join Date
    Jul 2000
    Posts
    243
    Hi kmesser

    thank you for the TO_CHAR(transdate,'Q') i was looking for this in the documentation and could not find it.

    that is way i did not answer you kmesser

  10. #10
    Join Date
    Feb 2001
    Posts
    30
    thanks all,

    According to kmesser data,
    if I specified department to data
    That means I need to get sum(transprice) by department
    and quarter.



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