SQL query to print occupation with print name with order

Question

Query an alphabetically ordered list of all names in OCCUPATIONS, immediately followed by the first letter of each profession as a parenthetical (i.e.: enclosed in parentheses).

For example:

AnActorName(A), ADoctorName(D), AProfessorName(P), and ASingerName(S)

Query the number of occurrences of each occupation in OCCUPATIONS. Sort the occurrences in ascending order, and output them in the following format:

SELECT name||'('||SUBSTR(occupation, 1, 1)||')'
FROM occupations
ORDER BY name

UNION

SELECT "There are a total of"|| count(*) from occupations group by occupation;

Answer 1

select concat(name, '(',left(occupation,1),')') 
from occupations 
order by name asc;

select 'There are a total of ' ,count(occupation) ,concat(lower(occupation), 's.') 
from occupations
group by occupation 
order by count(occupation) asc, occupation asc;

//status of Testcase passed

Answer 2

My Approach

  select CONCAT(Name,'(',substr(OCCUPATION,1,1),')') 
    from OCCUPATIONS 
order by Name asc;

  select CONCAT('There are a total of ',count(OCCUPATION),' ',lower(OCCUPATION),'s.') 
    from OCCUPATIONs group by OCCUPATION 
order by COUNT(OCCUPATION) asc,OCCUPATION asc;

Answer 3

sample input and output with code:

create table ns_occupations(name varchar(20));

insert into NS_OCCUPATIONS values('AActorName');

insert into NS_OCCUPATIONS values('ADoctorName');

insert into NS_OCCUPATIONS values('AProfessorName');

insert into NS_OCCUPATIONS values('ASingerName');


insert into NS_OCCUPATIONS values('ASingerName');

select * from NS_OCCUPATIONS;

SELECT name||'('||SUBSTR(name, 2, 1)||')'  shortname,count(*)  no_of_occupations
FROM ns_occupations
group by name||'('||SUBSTR(name, 2, 1)||')';

output:
ADoctorName(D)  1
AActorName(A)   1
AProfessorName(P)   1
ASingerName(S)  2

Answer 4

ORDER BY has to be last clause when you use UNION/UNION ALL:

SELECT name||'('||SUBSTR(occupation, 1, 1)||')' AS col
FROM occupations
union
select 'There are a total of'|| count(*) 
from occupations 
group by occupation
ORDER BY CASE WHEN col LIKE 'There are a total%' THEN 1 ELSE 0 END, col;

Answer 5

select name+'('+left(occupation,1)+')' from occupations order by name union select 'There are a total of '+convert(nvarchar,count(occupation))+' '+occupation+'s.' from occupations group by occupation order by count(occupation);

Answer 6

Use the following query. If you don't use UNION then it will display the column name for the second table, which is not the ask.

FROM OCCUPATIONS
ORDER BY NAME)

UNION 

(SELECT CONCAT('There are a total of ',COUNT(OCCUPATION),' ',LOWER(OCCUPATION),'s.')
FROM OCCUPATIONS
GROUP BY OCCUPATION
ORDER BY COUNT(OCCUPATION),OCCUPATION);

Answer 7

For Sql Server select Name+'('+substring(Occupation,1,1)+')' from Occupations order by Name

select 'There are a total of ' + cast(count() as varchar) +' '+ lower(Occupation) +'s.' from Occupations Group by Occupation order by Count()

Answer 8

SELECT CONCAT('There are a total of ', COUNT(*), ' ', LOWER(occupation), 's.') AS result
FROM occupations
GROUP BY occupation
ORDER BY COUNT(*);