aofe1337 aofe1337 - 6 months ago 10
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

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

vkp vkp
Answer

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
Comments