-1

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)
;
aofe1337
  • 67
  • 8
  • That kind of formatting is better done in the application (GUI), not in the SQL. –  May 28 '16 at 07:08

1 Answers1

1

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
Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58
  • what if you have multiple categories with the same amount of books? – aofe1337 May 28 '16 at 03:58
  • do you want to select all books for a category which have minimum number of rows in the table? – Vamsi Prabhala May 28 '16 at 04:00
  • example if business had 1 and sports had 1, I'd want to display both. Sorry - I should've made myself more clearer in the original post – aofe1337 May 28 '16 at 04:01
  • It's not just "Oracle" that uses `||` for string concatenation. The `||` is part of the SQL standard specification. –  May 28 '16 at 07:08