[코드카타] SQL 68 The PADS

[코드카타] SQL 68 The PADS

68 The PADS
https://www.hackerrank.com/challenges/the-pads/problem?isFullScreen=true

Generate the following two result sets:
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 ocurrences of each occupation in OCCUPATIONS. 
Sort the occurrences in ascending order, and output them in the following format:
There are a total of [occupation_count] [occupation]s.
where [occupation_count] is the number of occurrences of an occupation 
in OCCUPATIONS and [occupation] is the lowercase occupation name. 
If more than one Occupation has the same [occupation_count], 
they should be ordered alphabetically.
Note: There will be at least two entries in the table for each type 
of occupation.

Select concat(name,'(',left(occupation,1),')') names
From Occupations
union all
Select concat('There are a total of ',count(*),' ', lower(OCCUPATION),'s.') cnt
from OCCUPATIONS
group by occupation
order by 1

두 개의 쿼리문을 작성 후 union all로 수직 결합
조건 1 : 이름 옆에 직업의 첫글자를 작성하기
조건 2 : 직업별 수를 작성하기
해결 방법
1. concat을 통해 결합 concat(name,'(',left(occupation,1),')')
2. 직업별 수를 작성하기 위해 occupation 기준으로 그룹화
3. union all로 수직 결합
4. order by 에 대해서 주의 ( 쿼리별 order by 할 경우 적용 X )