Results 1 to 10 of 10

Thread: month sorting

  1. #1
    Join Date
    Sep 2009
    Posts
    34

    Unanswered: month sorting

    hi all
    thanks for guidance.
    today i have a problem that i have a table deposit in which i have two fields one is deposit_date and the other is deposit_amount now i need a report of the year i use this query
    select To_char(deposit_date,'month') as 'month', sum(deposit_amount) as 'amount' from deposit group by To_char(deposit_date,'month') and order by To_char(deposit_date,'month') asc

    it will give me result in this order
    month amount
    August $400
    . .
    . .
    . .
    September $1050


    while


    i want to print in this order
    month amount
    January $200
    February $50
    . .
    . .
    . .
    December $210

    plz help me how to remove this problem

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
     1* select ename, to_char(hiredate,'Month') from emp order by to_char(hiredate,'MM')
    SQL> /
    
    ENAME	   TO_CHAR(HIREDATE,'MONTH')
    ---------- ------------------------------------
    MILLER	   January
    ALLEN	   February
    WARD	   February
    JABBAR	   February
    RAFIQ	   February
    KARIN	   February
    SALAM	   February
    JONES	   April
    SCOTT	   April
    BLAKE	   May
    ADAMS	   May
    
    ENAME	   TO_CHAR(HIREDATE,'MONTH')
    ---------- ------------------------------------
    CLARK	   June
    MARTIN	   September
    TURNER	   September
    KING	   November
    FORD	   December
    JAMES	   December
    RAHIM	   December
    
    18 rows selected.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2009
    Posts
    34

    month sorting

    thanks for reply
    i used this
    select to_char(deposit_date,'Month'),sum(deposit) from deposit group by to_char(deposit,'Month') order by to_char(hiredate,'Month')
    this result is this
    sep 4000
    aug 5000
    dec 1000
    july 3000

    but

    i want to sort in this order
    july 3000
    aug 5000
    sep 4000
    dec 1000


    plz help me

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Look carefully at anacedent's post and code your query accordingly.
    He gave you the solution.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Sep 2009
    Posts
    34

    month sorting

    Hi all
    i used anacedent's query
    HTML Code:
    select to_char(deposit_date,'Month'),sum(deposit) from deposit group by to_char(deposit,'MM') order by to_char(hiredate,'MM')
    
    Give Error that 
    this is not a group function
    select to_char(deposit_date,'Month'),sum(deposit) from deposit group by [U]to_char(deposit,'MM')[/U] order by to_char(hiredate,'MM')
                                                                                           *
    now how to solve this
    Last edited by oryazi; 01-31-11 at 14:40.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Do you need to group by the deposit amount?

  7. #7
    Join Date
    Sep 2009
    Posts
    34

    month sorting

    i want to group by deposit month

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Does your group by clause do that?

  9. #9
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    You need to use the CORRECT group by:
    Code:
    SELECT To_char(deposit_date, 'Month'),
           SUM(deposit)
    FROM   deposit
    GROUP  BY To_char(deposit_date, 'Month')
    ORDER  BY To_char(deposit_date, 'MM')
    Last edited by LKBrwn_DBA; 02-01-11 at 11:21. Reason: Ooops small correction...
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  10. #10
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    in sqlplus
    column month noprint

    select to_char(deposit_date,'mm') month, to_char(deposit_date,'Month'),sum(deposit) from deposit group by to_char(deposit,'Month'),to_char(deposit_date,'mm' ) order by to_char(deposit_date,'mm');
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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