SQL query to print occupation with print name with order

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;


2 Answers
2

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


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

ORDER BY

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;


WHERE

Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy