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:
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.
(SELECT NAME, '(', SUBSTRING(OCCUPATION, 1, 1), ')' FROM OCCUPATIONS ORDER BY NAME) UNION ALL (SELECT COUNT(*) FROM OCCUPATIONS GROUP BY OCCUPATION ORDER BY ASEC);
ERROR 1222 (21000) at line 1: The used SELECT statements have a different number of columns
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;
Answered By – alexander.polomodov
Answer Checked By – Timothy Miller (BugsFixing Admin)