MySQL Query error using UNION/UNION ALL and Group By

Question

Generate the following two result sets:

1). 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).

2). 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 total [occupation_count] [occupation]s.

Table Name: Occupations

Total Columns: Two = 'Name' and 'Occupation', demo table is shown below:

Sample Output:

Ashely(P)
Christeen(P)
Jane(A)
Jenny(D)
Julia(A)
Ketty(P)
Maria(A)
Meera(S)
Priya(S)
Samantha(D)
There are total 2 doctors.
There are total 2 singers.
There are total 3 actors.
There are total 3 professors.

My Approach:

(SELECT NAME, '(', SUBSTRING(OCCUPATION, 1, 1), ')' 
    FROM OCCUPATIONS ORDER BY NAME) 
UNION ALL 
(SELECT COUNT(*) FROM OCCUPATIONS GROUP BY OCCUPATION ORDER BY ASEC);

Error:

ERROR 1222 (21000) at line 1: 
    The used SELECT statements have a different number of columns

Thank You!

Answer 1

You forgot to use CONCAT function to glue your selected data

Try something like this (also see this example on sqlfiddle):

(
  SELECT CONCAT(NAME, '(', SUBSTRING(OCCUPATION, 1, 1), ')') as THETEXT, '1' as SELECTNUMBER
    FROM OCCUPATIONS
)
UNION ALL 
(
  SELECT CONCAT('There are total ', COUNT(*),' ', OCCUPATION, (IF (COUNT(*) > 1, 's',''))) as THETEXT, '2' as SELECTNUMBER
   FROM OCCUPATIONS GROUP BY OCCUPATION
)
ORDER BY SELECTNUMBER ASC, THETEXT ASC;

Answer 2

I just tried on hackerrank and it works, You don't need to use Union.

select concat(name,'(',upper(substring(occupation,1,1)),')') from occupations
order by name;

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