aofe1337 aofe1337 - 1 year ago 93
SQL Question

SQL Format $ and Rounding

I'm trying to display the title, ISBN, Cost and category where the category has the least amount of books in it, while also trying to add a "$" in front of the output for cost while trying to round it to two decimal places.

Example for category =

Technology = 4
Family = 3
Sports = 2
Business = 4

In this case I want the output to display category Sports

Round( '$' + CAST(COST AS VARCHAR(15)) COST, 2),
GROUP By category
HAVING Category = ((Select Min(Category)
From (Select Count(Category) AS Category)
Group By Category)

vkp vkp
Answer Source

Oracle uses || for string concatenation. To get the categories with min counts you can use inline views, one to get the counts and the other to get the minimum count and join with the original table.

select b.TITLE, b.ISBN,
'$' || TO_CHAR(ROUND(b.COST,2)) cost, b.CATEGORY
From BOOKS b
join (select category,count(*) cnt from books group by category) ct
on b.category = ct.category
join (select min(cnt) mincnt 
      from (select count(*) cnt from books group by category)) minct
on ct.cnt = minct.mincnt
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download