Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: inserting the thousand seperator (,) in the output of a query

    Hi

    I have the following query but I would like to have the numbers appear with a ',' to make the numbers more readable.

    I know this can be done in Excel (which is where it should be done anyway) but just wondering if this can also be done at the query level:

    select datetime,
    sum(orginating_count) "Org Tot",
    sum(terminating_count) "Term Tot",
    ( sum(orginating_count)+sum(terminating_count) ) "SM Tot",
    round( ( sum(orginating_count)+sum(terminating_count) ) / 3600, 2) AVE
    from schema.table
    where datetime between trunc(sysdate)-1 and trunc(sysdate)-1/24
    and caption not in ('mar2','mar1','SMQ','SM','MQ')
    group by datetime

    I've tried using TO_CHAR,'999,999' which gives me what I want but only if I don't use SUM in the statement.

    Would appreciate any advice.

    Regards
    Sheraz

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by shajju
    I've tried using TO_CHAR,'999,999' which gives me what I want but only if I don't use SUM in the statement.
    This should work:
    Code:
    to_char(sum(orginating_count),'999,999') "Org Tot"

  3. #3
    Join Date
    Aug 2008
    Posts
    464

    Thanks and also...

    Thanks. Works a treat. Can I also ask how to use TO_CHAR in:

    ( sum(org_count)+sum(term_count) ) "SM Tot",

    and

    round( ( sum(org_count)+sum(term_count) ) / 3600, 2) avg_sm?

  4. #4
    Join Date
    Aug 2008
    Posts
    464
    I would like to thank you for your help. TO_CHAR has saved the day. Thank you very much.

    Note: I would like to close this thread now.

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    PHP Code:
    jmagnus@prod01select 100000 thenumber from dual;

     
    THENUMBER
    ----------
        
    100000

    jmagnus
    @prod01col thenumber format 999,999
    jmagnus
    @prod01> /

    THENUMBER
    ---------
      
    100,000 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Aug 2008
    Posts
    464

    Thanks...

    Thanks for all your replies.

    Duck

    The 'col thenumber format 999,999' works in sqlplus only right? Not in toad. Am I being very silly?

    Also, I take it 999,999 doesn't take care of values greater than 999,999?

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    No, it does not work in TOAD.

    No, it doesn't take care of values greater than 999,999. But nothing stops you to set column format differently.

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    TOAD you would go into SETTINGS and change the setting to display numeric values with commas. Considering you would export that to excel I really don't see an issue since excel can display numerics any way you want (unless you want to see the numbers in the cells with columns).

    if you want larger format then change 999,999 to 999,999,999,999,999
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Aug 2008
    Posts
    464

    Thank you

    Thanks guys. I have what I want

Posting Permissions

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