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

This should work:

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?

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.

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?

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.

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

Thanks guys. I have what I want