Use Select query and add its output to text in Oracle

Question

i have this table

Name      Occupation
Maria     Actor
Samantha  Doctor
John      Doctor
Kevin     Professor 

and i want to count the occurence of professor and output it this way :

There are a total of [occupation_count] [occupation]s.

i know how to count the number of occurences but i don't know how to output the message , can anyone help me ?

Answer 1

select 'There are '|| count(*) ||' '|| occupation
from jobs
group by occupation

Answer 2

You may use this without group by the aid of nested decode inside sum :

select 'There are a total of ' ||  sum(decode(occupation,'&occupation',1,0)) || ' ' || max(occupation) || 's' "Nr. of Prof's" 
     -- enter Professor whenever prompts, besides you may try other occupations as bind variable
  from  Occupations

Demo