Reputation: 65
I want to create a "sales report" for my SQL pizza restaurant database. When I try to run the SELECT query with sum(order_amount), the column isn't formatted. I'm using Oracle SQL. Here is the code:
column order_amount heading "Order Amount" format '$9,999,999.00';
select order_id, count(order_id) as "Number of Orders", sum(pizza_quantity) as "Pizzas Sold",
sum(order_amount) as "Total Profit"
from order_details group by order_id;
And here is the output:
SQL> select order_id, count(order_id) as "Number of Orders",sum(pizza_quantity) as "Pizzas Sold",
2 sum(order_amount) as "Total Profit"
3 from order_details group by order_id;
Order ID Number of Orders Pizzas Sold Total Profit
---------- ---------------- ----------- ------------
1 2 5 105
6 1 2 20
2 2 6 165
4 2 12 130
5 2 6 80
8 1 7 140
3 2 4 80
7 1 6 120
9 1 8 120
10 1 9 270
10 rows selected.
Is there any way to display the Total Profit column formatted as currency?
Upvotes: 3
Views: 4834
Reputation: 16001
If your question is about the SQL*Plus column formatting specifically, the issue was that you defined a format for a column named order_amount
but in the query it was called "Total Profit"
:
column order_amount heading "Order Amount" format '$9,999,999.00'
If you change your query so that the column you want is named order_amount
then the defined format will be used:
select order_id, count(order_id) as "Number of Orders", sum(pizza_quantity) as "Pizzas Sold"
, sum(order_amount) as order_amount
from order_details
group by order_id;
(By the way I doubt that the total order amount is all profit unless your costs are zero, in which case I'd like to invest in your pizza business.)
Upvotes: 1
Reputation: 65433
You might use number format modelling elements as FML9,999.99
or $9G990D00
for the second argument of to_char
function as in the following sample :
SQL> alter session set nls_currency='$';
SQL> alter session set NLS_NUMERIC_CHARACTERS = '.,';
SQL> with tab(order_ID,order_amount) as
(
select 1,255.51 from dual union all
select 1,750.33 from dual union all
select 6,420.39 from dual union all
select 2,790.00 from dual union all
select 2,375.05 from dual
)
select to_char(sum(order_amount),'FML9,999.99') as "Total Profit"
from tab
group by order_ID
union all
select to_char(sum(order_amount),'$9G990D00') as "Total Profit"
from tab
group by order_ID
Total Profit
$1,005.84
$420.39
$1,065.05
$1,005.84
$420.39
$1,065.05
Upvotes: 1
Reputation: 37527
You can try using to_char()
.
...
to_char(sum(order_amount), '$9,999,999.00')
...
Upvotes: 6