select Rpad(Rpad((Rpad(name,length(name)+1,'(')),length(name)+2,((substr(occupation,1,1)))),length(name)+3,')') from occupations order by name; select concat('There are a total of ',count(name)),concat(lower(occupation),'s.')from occupations group by occupation order by count(name);

SELECT CONCAT(Name, CASE WHEN Occupation = 'Doctor' THEN '(D)' WHEN Occupation = 'Professor' THEN '(P)' WHEN Occupation = 'Singer' THEN '(S)' WHEN Occupation = 'Actor' THEN '(A)' END) 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), Occupation;

SELECT NAME || '(' || LEFT(Occupation, 1) || ')' FROM OCCUPATIONS ORDER BY NAME;

SELECT 'There are a total of ' || COUNT() || ' ' || LOWER(Occupation) || 's.' FROM OCCUPATIONS GROUP BY Occupation ORDER BY COUNT();

This query worked for me.

SET NOCOUNT ON;

WITH CTE1 AS ( SELECT CONCAT(Name , '(', LEFT(Occupation,1) , ')') as name FROM OCCUPATIONS ),

CTE2 as ( SELECT * , Count(Occupation) over (Partition by Occupation) AS occ, Row_Number() over(partition by Occupation order by Name) as rn FROM OCCUPATIONS ),

CTE3 as ( select Name, Occupation, Occ from CTE2 where rn =1 ), CTE4 AS ( Select *, Row_Number() over(order by Occ,Occupation)as rn2 from CTE3 )

SELECT * from CTE1 union Select concat('There are a total of ', Occ ,' ' , lower(Occupation)+'s.') from CTE4 order by 1

go

SQL Server

SELECT CONCAT(NAME, '(', LEFT(OCCUPATION, 1), ')') FROM OCCUPATIONS ORDER BY NAME;
SELECT CONCAT('There are a total of ', A.CNT, ' ', A.OCCUPATION, 's.') FROM
(
    SELECT LOWER(OCCUPATION) OCCUPATION, J.CNT FROM OCCUPATIONS O
    CROSS APPLY
    (
        SELECT COUNT(OCCUPATION) CNT FROM OCCUPATIONS WHERE LOWER(OCCUPATION) = O.OCCUPATION GROUP BY OCCUPATION
    ) J
) A GROUP BY CNT, OCCUPATION ORDER BY CNT;